X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A3F5D563A4
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  9 Mar 2005 01:46:49 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 72451-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  9 Mar 2005 01:46:39 +0000 (GMT)
Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu
 [129.255.60.186])
	by svr1.postgresql.org (Postfix) with ESMTP id 68752563C2
	for <pgsql-performance@postgresql.org>;
	Wed,  9 Mar 2005 01:46:38 +0000 (GMT)
Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0])
	by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id j291kf326383;
	Tue, 8 Mar 2005 19:46:42 -0600
Message-ID: <422E557D.7030500@arbash-meinel.com>
Date: Tue, 08 Mar 2005 19:46:37 -0600
From: John A Meinel <john@arbash-meinel.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: James G Wilkinson <jgw@alpinegeophysics.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Optimization
References: <422E3D81.3000407@alpinegeophysics.com>
In-Reply-To: <422E3D81.3000407@alpinegeophysics.com>
X-Enigmail-Version: 0.89.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig9C17025D65B09BA78C75E059"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200503/150
X-Sequence-Number: 10855

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig9C17025D65B09BA78C75E059
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


--------------enig9C17025D65B09BA78C75E059
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (Cygwin)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCLlV9JdeBCYSNAAMRAoSyAKClXsPxacgTYhjhAzUiik5uIE4AJgCgy6FO
+UzLbTuP7opLvnAKsKSO/ww=
=8cBI
-----END PGP SIGNATURE-----

--------------enig9C17025D65B09BA78C75E059--