Index with all necessary columns - Postgres vs MSSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux