From: Manfred Koizar <mkoi-pg@aon.at>
To: Chantal Ackermann <chantal.ackermann@biomax.de>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: cost and actual time
Date: Wed, 19 Feb 2003 11:34:03 +0100
Message-ID: <dlm65v4kilkb0d2isk5rot23jcrngc3453@4ax.com> (raw)
In-Reply-To: <3E5350AE.7080408@biomax.de>
References: <web-2838434@davinci.ethosmedia.com>
<3E50BECC.9090107@biomax.de>
<pg325v0d34dn2c03gbg0qli11rqnotf642@4ax.com>
<3E520AD8.2000304@biomax.de>
<ajo45v4o0op0f3qnqh1le2rs79008fqp5u@4ax.com>
<3E5350AE.7080408@biomax.de>
Chantal,
I'm short of time right now. So just a few quick notes and a request
for more information. Next round tomorrow ...
On Wed, 19 Feb 2003 10:38:54 +0100, Chantal Ackermann
<chantal.ackermann@biomax.de> wrote:
>yeah, I noticed the difference between the two queries. actually, I am
>afraid of dropping the distinct cause I had results with duplicate rows
>(though I shall recheck when this is really the case). These are the
>table declarations and constraints: [...]
AFAICS there's no way to get duplicates, so no need for DISTINCT.
> we have to use bigint as type. is the join therefore
>such expensive?
No.
>we had a primary key occurrence_id on the occurrences tables but we
>noticed that we don't use it, so we didn't recreate it in the new
>database. is it possible that the postgres could work with it internally?
No.
> Nested Loop Total runtime: 53508.86 msec
>Merge Join: Total runtime: 113066.81 msec
>Hash Join: Total runtime: 439344.44 msec
>I changed the gene name at every run to avoid the caching.
You can't compare the runtimes unless you query for the same data.
Either run each query twice to make sure everything is cached or do
something like
tar cf /dev/null /some/big/directory
before each query to empty your disk cache. BTW, what is your
shared_buffers setting.
>disabling the hash join results again in a Nested Loop with very high
>cost but low runtime - I'm not sure if the latter is the consequence of
>caching.
Please send EXPLAIN ANALYZE results for all queries. Send it to me
off-list if you think its too long.
Servus
Manfred
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mkoi-pg@aon.at, chantal.ackermann@biomax.de, josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: cost and actual time
In-Reply-To: <dlm65v4kilkb0d2isk5rot23jcrngc3453@4ax.com>
References: <web-2838434@davinci.ethosmedia.com> <3E50BECC.9090107@biomax.de> <pg325v0d34dn2c03gbg0qli11rqnotf642@4ax.com> <3E520AD8.2000304@biomax.de> <ajo45v4o0op0f3qnqh1le2rs79008fqp5u@4ax.com> <3E5350AE.7080408@biomax.de> <dlm65v4kilkb0d2isk5rot23jcrngc3453@4ax.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