Re: count(*), EXISTS, indexes

> 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/>