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.profileand ~rcp.psig ~<= rp.psig and rcp.filter='{734,1944}');                                              QUERY
PLAN
---------------------------------------------------------------------------------------------------- Aggregate
(cost=1544943.75..1544943.75rows=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.06rows=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)
(actualtime=0.08..0.17 rows=1 loops=1)                Filter: (filter = '{734,1944}'::text)          ->  Index Scan
usingrprofile_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/>