From: John A Meinel <john@arbash-meinel.com>
To: James G Wilkinson <jgw@alpinegeophysics.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Optimization
Date: Tue, 08 Mar 2005 19:46:37 -0600
Message-ID: <422E557D.7030500@arbash-meinel.com> (raw)
In-Reply-To: <422E3D81.3000407@alpinegeophysics.com>
References: <422E3D81.3000407@alpinegeophysics.com>
James G Wilkinson wrote:
> All,
>
...
> Firstly, I am frankly mystified on how to interpret all this. If anyone
> could point me to a document or two that will help me decipher this,
> I will greatly appreciate it.
>
I assume you have looked at:
http://www.postgresql.org/docs/8.0/static/performance-tips.html
And didn't find it helpful enough. I'm not really sure what help you are
asking. Are you saying that this query is performing slowly and you want
to speed it up? Or you just want to understand how to interpret the
output of explain?
> Secondly, I have figured out that SEQ SCANs are typically bad. I am
> concerned that a SEQ SCAN is being performed on 'biogenic_beld3_data'
> which is the largest table in the query. I would rather have a SEQ SCAN
> be performed on 'tmpgrid' which contains the keys that subset the data
> from 'biogenic_beld3_data.' Is this naive on my part?
It depends how much data is being extracted. If you have 1,000,000 rows,
and only need 10, then an index scan is wonderful. If you need 999,999,
then a sequential scan is much better (the break even point is <10%)
From the explain, it thinks it is going to be needing 5,637 rows from
biogenic_beld3_data, what is that portion relative to the total?
The values at least look like you've run vacuum analyze. Have you tried
running "explain analyze" instead of just explain? Then you can see if
the planners estimates are accurate.
If you want some help to force it, you could try a subselect query.
Something like:
select * from biogenic_beld3_data b where b.beld3_icell = (select
b_icell from tmpgrid_pk) and b.beld3_jcell = (select b_jcell from
tmpgrid_pk);
>
> Thirdly, I have run EXPLAIN on other queries that report back a
> GroupAggregate Cost=<low 300,000s> that runs in about 30 minutes
> on my relatively highend linux machine. But when I run this particular
> query, it takes on the order of 90 minutes to complete. Any thoughts
> on why this happens will be appreciated.
>
Remember cost is in terms of page fetches, not in seconds.
Probably it is just an issue of postgres mis-estimating the selectivity
of one of your queries.
Also, you have a fairly complex SUM occurring involving 4
multiplications on an estimated 150,000 rows. While doesn't seem like it
should take 90 minutes, it also isn't a trivial operation.
> Finally, if anyone can be so kind as to provide insight on how to better
> optimize this query, I will, again, be deeply grateful.
>
> Thanks in advance.
>
> terrakit
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
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: john@arbash-meinel.com, jgw@alpinegeophysics.com
Subject: Re: Query Optimization
In-Reply-To: <422E557D.7030500@arbash-meinel.com>
* 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