X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 803C947635A for ; Fri, 11 Apr 2003 17:06:30 -0400 (EDT) Received: from nycsmtp3out.rdc-nyc.rr.com (nycsmtp3out.rdc-nyc.rr.com [24.29.99.224]) by postgresql.org (Postfix) with ESMTP id 593DC476351 for ; Fri, 11 Apr 2003 17:06:29 -0400 (EDT) Received: from math-hat.com (66-65-68-41.nyc.rr.com [66.65.68.41]) by nycsmtp3out.rdc-nyc.rr.com (8.12.1/Road Runner SMTP Server 1.0) with SMTP id h3BL7KLa011742; Fri, 11 Apr 2003 17:07:22 -0400 (EDT) Received: (nullmailer pid 23177 invoked by uid 1000); Fri, 11 Apr 2003 21:06:25 -0000 To: josh@agliodbs.com Cc: pgsql-sql@postgresql.org Subject: Re: count(*), EXISTS, indexes Mail-Copies-To: never From: Itai Zukerman Date: Fri, 11 Apr 2003 17:06:25 -0400 In-Reply-To: <200304111353.50162.josh@agliodbs.com> (Josh Berkus's message of "Fri, 11 Apr 2003 13:53:50 -0700") Message-ID: <871y08sq0e.fsf@matt.w80.math-hat.com> User-Agent: Gnus/5.090018 (Oort Gnus v0.18) Emacs/21.2 (gnu/linux) References: <874r54sr8q.fsf@matt.w80.math-hat.com> <200304111353.50162.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-22.6 required=5.0 tests=BAYES_01,IN_REP_TO,REFERENCES,USER_AGENT_GNUS_UA autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/183 X-Sequence-Number: 12884 > Interesting. Can you post your Postges version, and EXPLAIN ANALYZE for each > of those queries? Sure. Here's what I want: # explain select sum(weight) from rprofile rp where exists (select 1 from rcount_prof rcp where rcp.profile ~<= rp.profile and ~rcp.psig ~<= rp.psig and rcp.filter='{734,1944}'); QUERY PLAN ---------------------------------------------------------------------------------------------------- Aggregate (cost=1544943.75..1544943.75 rows=1 width=4) -> Seq Scan on rprofile rp (cost=0.00..1544255.00 rows=275500 width=4) Filter: (subplan) SubPlan -> Seq Scan on rcount_prof rcp (cost=0.00..2.70 rows=1 width=0) Filter: ((profile ~<= $0) AND ((~ psig) ~<= $1) AND (filter = '{734,1944}'::text)) Here's a version that uses the index, but over-counts: # explain analyze select sum(weight) from rprofile rp, rcount_prof rcp where rcp.profile ~<= rp.profile and ~rcp.psig ~<= rp.psig and rcp.filter='{734,1944}'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=2245.07..2245.07 rows=1 width=1001) (actual time=1183.53..1183.53 rows=1 loops=1) -> Nested Loop (cost=0.00..2245.06 rows=1 width=1001) (actual time=0.44..1156.98 rows=23338 loops=1) Join Filter: ("outer".profile ~<= "inner".profile) -> Seq Scan on rcount_prof rcp (cost=0.00..2.44 rows=1 width=287) (actual time=0.08..0.17 rows=1 loops=1) Filter: (filter = '{734,1944}'::text) -> Index Scan using rprofile_profile_idx on rprofile rp (cost=0.00..2232.98 rows=551 width=714) (actual time=0.25..1083.15 rows=23385 loops=1) Index Cond: ((~ "outer".psig) ~<= rp.psig) Total runtime: 1183.67 msec $ psql --version psql (PostgreSQL) 7.3.2 Running on RedHat. (It takes a long time to run the first select, so I left off the analyze.) -- Itai Zukerman