Re: [PERFORM] Using Between

2010-09-23 Thread mark
>>> The question is how can we make it faster. >>If there's just one region ID for any given postal code, you might try >>adding a column to vehicleused and storing the postal codes there. >>You could possibly populate that column using a trigger; probably it >>doesn't change unless the postalcode

Re: [PERFORM] Using Between

2010-09-22 Thread Ozer, Pam
Thank you. I will take a look at those suggestions. -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, September 22, 2010 9:27 AM To: Ozer, Pam Cc: Craig James; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Using Between On Wed, Sep 22, 2010

Re: [PERFORM] Using Between

2010-09-22 Thread Robert Haas
On Wed, Sep 22, 2010 at 11:18 AM, Ozer, Pam wrote: > The question is how can we make it faster. If there's just one region ID for any given postal code, you might try adding a column to vehicleused and storing the postal codes there. You could possibly populate that column using a trigger; probab

Re: [PERFORM] Using Between

2010-09-22 Thread Ozer, Pam
The question is how can we make it faster. -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Wednesday, September 22, 2010 3:52 AM To: Ozer, Pam Cc: Craig James; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Using Between On Tue, Sep 21, 2010 at 4:04

Re: [PERFORM] Using Between

2010-09-22 Thread Robert Haas
On Tue, Sep 21, 2010 at 4:04 PM, Ozer, Pam wrote: > There are 850,000 records in vehicleused.  And the database is too big to be > kept in memory. Ah. So in other words, you are retrieving about half the rows in that table. For those kinds of queries, using the index tends to actually be slowe

Re: [PERFORM] Using Between

2010-09-21 Thread Ozer, Pam
es = DEBUG1 #log_min_duration_statement = 1000 #log_statement = all #log_temp_files = 128 #log_lock_waits = on #log_line_prefix = '%m %u %d %h %p %i %c %l %s' #log_duration = on #debug_print_plan = on -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: Tuesday,

Re: [PERFORM] Using Between

2010-09-21 Thread Robert Haas
On Mon, Aug 30, 2010 at 12:51 PM, Ozer, Pam wrote: > Yes.  ANALYZE was run after we loaded the data.  Thanks for your > assistance > Here is the full Query. > > select distinct VehicleUsed.VehicleUsedId as VehicleUsedId , >  VehicleUsed.VehicleUsedDisplayPriority as VehicleUsedDisplayPriority , >

Re: [PERFORM] Using Between

2010-08-30 Thread Ozer, Pam
26) Total runtime: 1945.244 ms -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Craig James Sent: Friday, August 27, 2010 5:42 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Using Between On 8/27/

Re: [PERFORM] Using Between

2010-08-27 Thread Craig James
On 8/27/10 5:21 PM, Ozer, Pam wrote: I have a query that Select Distinct VehicleId From Vehicle Where VehicleMileage between 0 and 15000. I have an index on VehicleMileage. Is there another way to put an index on a between? The index is not being picked up. It does get picked up when I run

[PERFORM] Using Between

2010-08-27 Thread Ozer, Pam
I have a query that Select Distinct VehicleId >From Vehicle Where VehicleMileage between 0 and 15000. I have an index on VehicleMileage. Is there another way to put an index on a between? The index is not being picked up. It does get picked up when I run Select Distinct VehicleI