From: Michael Glaesemann <grzm@seespotcode.net>
To: Ketema <ketema@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Improving Query
Date: Tue, 30 Oct 2007 08:31:46 -0500
Message-ID: <BB8E6D54-3814-4159-B173-9A541333483E@seespotcode.net> (raw)
In-Reply-To: <1193746737.222960.206150@50g2000hsm.googlegroups.com>
References: <1193746737.222960.206150@50g2000hsm.googlegroups.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: grzm@seespotcode.net, ketema@gmail.com
Subject: Re: Improving Query
In-Reply-To: <BB8E6D54-3814-4159-B173-9A541333483E@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox