Re: Improving Query

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
>
>