Skip site navigation (1) Skip section navigation (2)

Re: Improving Query

From: Ketema Harris <ketema(at)gmail(dot)com>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Improving Query
Date: 2007-10-30 14:15:34
Message-ID: 257E690A-5A28-4C07-A011-59500E5708D4@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Oct 30, 2007, at 9:31 AM, Michael Glaesemann wrote:

>
> On Oct 30, 2007, at 7:18 , Ketema wrote:
>
>> here is the execution plan:
>
> I've put this online here:
>
> http://explain-analyze.info/query_plans/1259-ketema-2007-10-30
>
>> I have attached an erd of the tables used in this query.  If it is
>> stripped out it can be viewed here: http://www.ketema.net/ 
>> provision_list_tables_erd.jpg
>>
>> My concern is with the sort step that takes 15 seconds by itself:
>>
>> ->  Sort  (cost=1235567017.53..1238002161.29 rows=974057502  
>> width=290)
>> (actual time=16576.997..16577.513 rows=3366 loops=1)
>
> What jumps out at me is the huge difference in estimated and  
> returned rows, and the huge cost estimates. Have you analyzed  
> recently?
Yes.  I run vacuum FULL ANALYZE VERBOSE every two days with a cron job.

I am running again now any way.
>
> Do you have enable_seqscan disabled? It appears so, due to the high  
> cost here:
>
> -> Seq Scan on order_details (cost=100000000.0..100000012.45  
> rows=35 width=199) (actual time=0.001..0.001 rows=0 loops=1)
>
> http://explain-analyze.info/query_plans/1259- 
> ketema-2007-10-30#node-3594
>
> What does it look like with seqscan enabled?
it was disabled.  new plan posted here:

http://explain-analyze.info/query_plans/1261-provision-list-seq-scan- 
enabled
>
>
>> 2)Create Views of the counts and the sub select...is this any faster
>> as the view is executed at run time anyway?
>
> Views aren't materialized: it's like inlining the definition of the  
> view itself in the query.
>
>> 3)Create actual tables of the sub select and aggregates...How would
>> this be maintained to ensure it was always accurate?
>
> One way would be to update the summaries using triggers. Hopefully  
> you won't need to do this after analyzing and perhaps tweaking your  
> server configuration.
>
> Unfortunately I don't have the time to look at the query plan in  
> more detail, but I suspect there's a better way to get the results  
> you're looking for.
>
> Michael Glaesemann
> grzm seespotcode net
>
>


In response to

pgsql-performance by date

Next:From: Marc SchablewskiDate: 2007-10-30 14:21:03
Subject: Re: Optimizing PostgreSQL for Windows
Previous:From: Ketema HarrisDate: 2007-10-30 13:42:02
Subject: Re: Improving Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group