X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 419B4474E53 for ; Mon, 17 Feb 2003 11:22:08 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 9AA47474E42 for ; Mon, 17 Feb 2003 11:22:07 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1HGLu5u009094; Mon, 17 Feb 2003 11:21:57 -0500 (EST) To: Chantal Ackermann Cc: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: cost and actual time In-reply-to: <3E50BECC.9090107@biomax.de> References: <3E50BECC.9090107@biomax.de> Comments: In-reply-to Chantal Ackermann message dated "Mon, 17 Feb 2003 11:51:56 +0100" Date: Mon, 17 Feb 2003 11:21:56 -0500 Message-ID: <9093.1045498916@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/109 X-Sequence-Number: 1165 Chantal Ackermann writes: > the gene_id for 'igg' occurres 110637 times in gene_occurrences, it is > the most frequent. I think the problem here is that the planner doesn't know that (and probably can't without some kind of cross-table statistics apparatus). It's generating a plan based on the average frequency of gene_ids, which is a loser for this outlier. Probably the most convenient way to do better is to structure things so that the reduction from gene name to gene_id is done before the planner starts to develop a plan. Instead of joining to gene, consider this: create function get_gene_id (text) returns int as -- adjust types as needed 'select gene_id from gene where gene_name = $1' language sql immutable strict; -- in 7.2, instead say "with (isCachable, isStrict)" EXPLAIN ANALYZE SELECT tmp.disease_name, count(tmp.disease_name) AS cnt FROM (SELECT DISTINCT disease.disease_name, disease_occurrences.sentence_id FROM disease, gene_occurrences, disease_occurrences WHERE gene_occurrences.sentence_id=disease_occurrences.sentence_id AND get_gene_id('igg')=gene_occurrences.gene_id AND disease.disease_id=disease_occurrences.disease_id) AS tmp GROUP BY tmp.disease_name ORDER BY cnt DESC; Now get_gene_id() isn't really immutable (unless you never change the gene table) but you have to lie and pretend that it is, so that the function call will be constant-folded during planner startup. The planner will then see something like gene_occurrences.gene_id = 42 and it will have a much better shot at determining the number of rows this matches. regards, tom lane