Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 915C364FD0F for ; Wed, 19 Nov 2008 07:51:55 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36588-02 for ; Wed, 19 Nov 2008 07:51:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f12.google.com (mail-gx0-f12.google.com [209.85.217.12]) by mail.postgresql.org (Postfix) with ESMTP id 291DF64FD99 for ; Wed, 19 Nov 2008 07:51:51 -0400 (AST) Received: by gxk5 with SMTP id 5so186923gxk.19 for ; Wed, 19 Nov 2008 03:51:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type; bh=epKUgxGDFm1BxSTOHgaV4B+skId2lgtAgxDAVOLliCY=; b=PoanYAkT3X0PhR2AYFubDvAO/DEBxr+2OFp/31ijtnSb4jXkxnxk56PiF3hJ7NmL7L WkiOyT/guHF1bb7emyTCMlshhdy4/7JKRNg57ubj+zg2XyJrcr7rrVw4V/MlJQoXcQL9 NwqvqAa0lmlPbWVwV97sQklZ14RBRBmIYL4bg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=KPP+8rHTY0SvqgDY/5T32vMXDyVKVMJdTnaWEDOrTvE5gRe/YQhs+uxMn/j1uEfwsT G3VivBbtUEGzPvDXuiB49npk+mXWUD9X2FLTHK2hvTODbQM5vCHucHkGM1cv5hKeNwWd yiePzNwXE2qQMeAgx/rgruXXjvH6SrCjuD4DM= Received: by 10.64.243.19 with SMTP id q19mr983954qbh.50.1227095507936; Wed, 19 Nov 2008 03:51:47 -0800 (PST) Received: by 10.65.22.1 with HTTP; Wed, 19 Nov 2008 03:51:47 -0800 (PST) Message-ID: <331e40660811190351n6bde6caic5fd8cc3a0f777d2@mail.gmail.com> Date: Wed, 19 Nov 2008 13:51:47 +0200 From: "=?ISO-8859-5?B?svbi0Nv22SDC2Nzn2OjY3Q==?=" To: pgsql-performance@postgresql.org Subject: PostgreSQL NOT IN performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_73357_21711985.1227095507928" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200811/160 X-Sequence-Number: 31891 ------=_Part_73357_21711985.1227095507928 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello. It's second query rewrite postgresql seems not to handle - making EXCEPT from NOT IT. Here is an example: Preparation: drop table if exists t1; drop table if exists t2; create temporary table t1(id) as select (random()*100000)::int from generate_series(1,200000) a(id); create temporary table t2(id) as select (random()*100000)::int from generate_series(1,100000) a(id); analyze t1; analyze t2; Query 1: select * from t1 where id not in (select id from t2); Plan: "Seq Scan on t1 (cost=1934.00..164105319.00 rows=100000 width=4)" " Filter: (NOT (subplan))" " SubPlan" " -> Materialize (cost=1934.00..3325.00 rows=100000 width=4)" " -> Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4)" Query 2 (gives same result as Q1): select * from t1 except all (select id from t2); Plan: "SetOp Except All (cost=38721.90..40221.90 rows=30000 width=4)" " -> Sort (cost=38721.90..39471.90 rows=300000 width=4)" " Sort Key: "*SELECT* 1".id" " -> Append (cost=0.00..7328.00 rows=300000 width=4)" " -> Subquery Scan "*SELECT* 1" (cost=0.00..4885.00 rows=200000 width=4)" " -> Seq Scan on t1 (cost=0.00..2885.00 rows=200000 width=4)" " -> Subquery Scan "*SELECT* 2" (cost=0.00..2443.00 rows=100000 width=4)" " -> Seq Scan on t2 (cost=0.00..1443.00 rows=100000 width=4)" If I am correct, planner simply do not know that he can rewrite NOT IN as "EXCEPT ALL" operator, so all NOT INs when list of values to remove is long takes very much time. So the question is: I am willing to participate in postgresql development because it may be easier to fix planner then to rewrite all my queries :). How can I? (I mean to work on query planner enhancements by providing new options of query rewrite, not to work on other thing nor on enhancing planner in other ways, like better estimations of known plans). ------=_Part_73357_21711985.1227095507928 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello.

It's second query rewrite postgresql seems not to handle - making EXCEPT from NOT IT.
Here is an example:
Preparation:

drop table if exists t1;
drop table if exists t2;
create temporary table t1(id) as
select
(random()*100000)::int from generate_series(1,200000) a(id);

create temporary table t2(id) as
select
(random()*100000)::int from generate_series(1,100000) a(id);
analyze t1;
analyze t2;

Query 1:
select * from t1 where id not in (select id from t2);
Plan:
"Seq Scan on t1  (cost=1934.00..164105319.00 rows=100000 width=4)"
"  Filter: (NOT (subplan))"
"  SubPlan"
"    ->  Materialize  (cost=1934.00..3325.00 rows=100000 width=4)"
"          ->  Seq Scan on t2  (cost=0.00..1443.00 rows=100000 width=4)"

Query 2 (gives same result as Q1):
select * from t1 except all (select id from t2);
Plan:
"SetOp Except All  (cost=38721.90..40221.90 rows=30000 width=4)"
"  ->  Sort  (cost=38721.90..39471.90 rows=300000 width=4)"
"        Sort Key: "*SELECT* 1".id"
"        ->  Append  (cost=0.00..7328.00 rows=300000 width=4)"
"              ->  Subquery Scan "*SELECT* 1"  (cost=0.00..4885.00 rows=200000 width=4)"
"                    ->  Seq Scan on t1  (cost=0.00..2885.00 rows=200000 width=4)"
"              ->  Subquery Scan "*SELECT* 2"  (cost=0.00..2443.00 rows=100000 width=4)"
"                    ->  Seq Scan on t2  (cost=0.00..1443.00 rows=100000 width=4)"

If I am correct, planner simply do not know that he can rewrite NOT IN as "EXCEPT ALL" operator, so all NOT INs when list of values to remove is long takes very much time.
So the question is: I am willing to participate in postgresql development because it may be easier to fix planner then to rewrite all my queries :). How can I? (I mean to work on query planner enhancements by providing new options of query rewrite, not to work on other thing nor on enhancing planner in other ways, like better estimations of known plans).



------=_Part_73357_21711985.1227095507928--