Re: Improving Query

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?

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?

> 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