[PERFORM] Slow Query

I am new to Postgres and I am trying to understand the Explain Analyze so I can tune the following query. I run the same query using mysql and it takes less than 50ms. I run it on postgres and it takes 10 seconds. I feel like I am missing something very obvious. (VehicleUsed is a big table over 7

Re: [PERFORM] Slow Query

Thank you all for your help. I will also get the rest of the information so it will be more clear. From: Greg Smith [mailto:g...@2ndquadrant.com] Sent: Friday, August 27, 2010 10:31 AM To: Ozer, Pam Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow Query Ozer, Pam wrote

[PERFORM] Using Between

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

Re: [PERFORM] Using Between

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

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

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

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

[PERFORM] Slow Query- Simple taking

I have the following query running on 8.4, which takes 3516 ms. It is very straight forward. It brings back 116412 records. The explain only takes 1348ms select VehicleUsed.VehicleUsedId as VehicleUsedId , VehicleUsed.VehicleUsedDisplayPriority as VehicleUsedDisplayPriority , VehicleUsed

Re: [PERFORM] Slow Query- Simple taking

On mysql the same query only takes milliseconds not seconds. That's a big difference. -Original Message- From: Kevin Grittner [mailto:kevin.gritt...@wicourts.gov] Sent: Tuesday, October 19, 2010 1:59 PM To: Ozer, Pam; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow

[PERFORM] Slow Query- Bad Row Estimate

I have the following query: select distinct Region.RegionShort as RegionShort ,County.County as County from Region join PostalCodeRegionCountyCity on (PostalCodeRegionCountyCity.RegionId=Region.RegionId) join DealerGroupGeoCache on (DealerGroupGeoCache.RegionId=PostalCodeRegionCountyCity

[PERFORM] Slow Query- Bad Row Estimate

Unfortunately I have not received a response on this question. Is more information needed? Does anyone have any ideas why the estimates may be bad? Or what I might be able to do to speed this up? thanks From: Ozer, Pam Sent: Tuesday, October 26, 2010 4:27 PM To: 'pgsql-perfor

Re: [PERFORM] Slow Query- Bad Row Estimate

I am not sure what you mean by reformulate the data representation. Do you mean do I have to join on all three columns? -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Friday, October 29, 2010 2:18 PM To: Ozer, Pam Cc: pgsql-performance@postgresql.org Subject: Re

Re: [PERFORM] Slow Query- Bad Row Estimate

pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Josh Berkus Sent: Friday, October 29, 2010 2:10 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow Query- Bad Row Estimate On 10/29/10 1:54 PM, Ozer, Pam wrote: > "

Re: [PERFORM] Slow Query- Bad Row Estimate

Yes. The default statistics target was at 1000. So that would be what the column was using correct? -Original Message- From: Josh Berkus [mailto:j...@agliodbs.com] Sent: Friday, October 29, 2010 2:55 PM To: Ozer, Pam Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow

Re: [PERFORM] Slow Query- Bad Row Estimate

: Ozer, Pam Cc: Josh Berkus; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Slow Query- Bad Row Estimate "Ozer, Pam" writes: > Yes. The default statistics target was at 1000. So that would be what the column was using correct? But you evidently didn't have stats.

[PERFORM] Two servers - One Replicated - Same query

I have two servers one has replication the other does not. The same query on both servers. One takes 225seconds on the replicated server the first time it runs and only 125ms on the other server the first time it runs. The second time you execute the query it drops to the 125ms. They are using th