pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chantal Ackermann <chantal.ackermann@biomax.de>
To: Manfred Koizar <mkoi-pg@aon.at>
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 10:38:54 +0100
Message-ID: <3E5350AE.7080408@biomax.de> (raw)
In-Reply-To: <ajo45v4o0op0f3qnqh1le2rs79008fqp5u@4ax.com>
References: <web-2838434@davinci.ethosmedia.com>
	<3E50BECC.9090107@biomax.de>
	<pg325v0d34dn2c03gbg0qli11rqnotf642@4ax.com>
	<3E520AD8.2000304@biomax.de>
	<ajo45v4o0op0f3qnqh1le2rs79008fqp5u@4ax.com>

hello Manfred,

> ... for joining both occurrences:  The "-> Nested Loop" takes two
> tables (the "-> Index Scans") as input and produces one table as
> output which is again used as input for the "-> Hash Join" above it.

as I am testing with the most frequent gene names (= the gene_ids that 
are the most frequent in the occurrences tables) this is a very 
expensive join. whenever I try a less frequent gene_id the runtime is 
shorter (though I haven't tested especially with less frequent gene_ids, 
yet. my focus is on making the searches for the most frequent genes 
faster as these are probably the ones that are searched for a lot.)

> There is no DISTINCT here.  This is equvalent to your first query, iff
> the following unique constraints are true:
> 	(gene_id, sentence_id) in gene_occurrences
> 	(disease_id, sentence_id) in disease_occurrences
> 	(disease_id) in disease
> 
> If they are, you don't need a sub-select (unless I'm missing
> something, please double-check):

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:

relate=# \d gene
         Table "public.gene"
    Column    |  Type   | Modifiers
-------------+---------+-----------
  gene_id     | integer | not null
  gene_name   | text    | not null
  gene_syn_id | integer | not null
Indexes: gene_pkey primary key btree (gene_id),
          gene_name_uni unique btree (gene_name),
          gene_uni unique btree (gene_name, gene_syn_id),
          gene_syn_idx btree (gene_syn_id)

(disease looks the same)

relate_01=# \d gene_occurrences
   Table "public.gene_occurrences"
    Column    |  Type   | Modifiers
-------------+---------+-----------
  sentence_id | bigint  | not null
  gene_id     | integer | not null
  puid        | integer | not null
Indexes: gene_occ_uni unique btree (sentence_id, gene_id),
          gene_occ_id_i btree (gene_id)

relate_01=# \d disease_occurrences
Table "public.disease_occurrences"
    Column    |  Type   | Modifiers
-------------+---------+-----------
  sentence_id | bigint  | not null
  disease_id  | integer | not null
  puid        | integer | not null
Indexes: disease_occ_uni unique btree (sentence_id, disease_id),
          disease_occ_id_i btree (disease_id)

sentence_id and gene/disease_id are connected in a n:m relation.
as sentence_id is the primary key of a table with more than 50 million 
rows, we decided not to use a serial as primary key but to use a unique 
combination of two existing values. as this combination is to long for 
an ordinary int, we have to use bigint as type. is the join therefore 
such expensive?

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?

> Play with enable_xxxx to find out which join method provides the best
> performance for various gene_ids.  Then we can start to fiddle with
> run-time parameters to help the optimizer choose the right plan.

this would be VERY helpful! :-)

I played around and this is the result:

EXPLAIN ANALYZE
SELECT d.disease_id, d.sentence_id
    FROM gene_occurrences g, disease_occurrences d
   WHERE g.sentence_id = d.sentence_id
     AND g.gene_id = get_gene_id([different very frequent gene names]);

choice of the planner: Nested Loop
  Total runtime: 53508.86 msec

set enable_nextloop to false;
Merge Join:  Total runtime: 113066.81 msec

set enable_mergejoin to false;
Hash Join: Total runtime: 439344.44 msec

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. I changed the gene name at every run to avoid the caching.

So the Nested Loop is obiously the best way to go?

For comparison: a less frequent gene (occurres 6717 times in 
gene_occurrences)
outputs the following query plan:

 
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------- 
Nested Loop  (cost=0.00..41658.69 rows=12119 width=20) (actual 
time=87.01..19076.62 rows=1371 loops=1)
    ->  Index Scan using gene_occ_id_i on gene_occurrences g 
(cost=0.00..10754.08 rows=7514 width=8) (actual time=35.89..10149.14 
rows=6717 loops=1)
          Index Cond: (gene_id = 16338)
    ->  Index Scan using disease_occ_uni on disease_occurrences d 
(cost=0.00..4.09 rows=2 width=12) (actual time=1.32..1.32 rows=0 loops=6717)
          Index Cond: ("outer".sentence_id = d.sentence_id)
  Total runtime: 19078.48 msec

> Usually you set a low random_page_cost value (the default is 4) if you
> want to favour index scans where the optimizer tends to use sequential
> scans.  Was this your intention?

No, not really. I found a posting in the archives where one would 
suggest reducing this parameter, so I tried it. I don't think it had any 
perceptiple effect.

>>cpu_tuple_cost = 0.01           # (same), default 0.01
>>cpu_index_tuple_cost = 0.00001  # (same), default 0.001
>>cpu_operator_cost = 0.005       # (same), default 0.0025
> 
> 
> Just out of curiosity:  Are these settings based on prior experience?

Nope. Same as above. I changed these variables only two days ago for 
what I recall. Untill than I had them at their default.

Regards,
Chantal




view thread (11+ messages)  latest in thread

Message-ID: <3E5350AE.7080408@biomax.de>
Permalink:  ../3E5350AE.7080408@biomax.de/
Also on:    postgresql.org/message-id/3E5350AE.7080408@biomax.de

 · 

reply

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: chantal.ackermann@biomax.de, mkoi-pg@aon.at, josh@agliodbs.com, tgl@sss.pgh.pa.us
  Subject: Re: cost and actual time
  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>

* 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