Hi,
I have a table in Postgres like:
CREATE TABLE test
(
id integer,
dtstamp timestamp without time zone,
rating real
)
CREATE INDEX test_all
ON test
USING btree
(id , dtstamp , rating);
My db has around 200M rows and I have reduced my test select statement down to:
SELECT count(1) FROM test
WHERE id in (58,83,88,98,124,141,170,195,202,252,265,293,305,331,348)
AND dtstamp between cast('2011-10-19 08:00:00' as timestamp) and cast('2011-10-19 16:00:00' as timestamp)
In Postgres this takes about 23 sec.
In MSSQL this takes about 1 sec.
MSSQL only accesses the index and does not access the table it self (uses only index scan)
Postgres has the following plan:
"Aggregate (cost=130926.24..130926.25 rows=1 width=0)"
" -> Bitmap Heap Scan on test (cost=1298.97..130832.92 rows=37330 width=0)"
" Recheck Cond: ((id = ANY ('{58,83,88,98,124,141,170,195,202,252,265,293,305,331,348}'::integer[])) AND (dtstamp >= '2011-10-19 08:00:00'::timestamp without time zone) AND (dtstamp <= '2011-10-19 16:00:00'::timestamp without time zone))"
" -> Bitmap Index Scan on test_all (cost=0.00..1289.64 rows=37330 width=0)"
" Index Cond: ((id = ANY ('{58,83,88,98,124,141,170,195,202,252,265,293,305,331,348}'::integer[])) AND (dtstamp >= '2011-10-19 08:00:00'::timestamp without time zone) AND (dtstamp <= '2011-10-19 16:00:00'::timestamp without time zone))"
The results are disappointing since I want to switch to Postgres but I have not been able to force Postgres to only use the index :-(
Any hints that may lead me back on track?
Thanks,
- Gummi