From: Itai Zukerman <zukerman@math-hat.com>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: count(*), EXISTS, indexes
Date: Fri, 11 Apr 2003 17:06:25 -0400
Message-ID: <871y08sq0e.fsf@matt.w80.math-hat.com> (raw)
In-Reply-To: <200304111353.50162.josh@agliodbs.com>
References: <874r54sr8q.fsf@matt.w80.math-hat.com>
<200304111353.50162.josh@agliodbs.com>
> 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 <http://www.math-hat.com/~zukerman/;
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: zukerman@math-hat.com, josh@agliodbs.com
Subject: Re: count(*), EXISTS, indexes
In-Reply-To: <871y08sq0e.fsf@matt.w80.math-hat.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