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