Index with all necessary columns - Postgres vs MSSQL

From: Gudmundur Johannesson <gudmundur(dot)johannesson(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Index with all necessary columns - Postgres vs MSSQL
Date: 2012-02-01 17:10:56
Message-ID: CAHNvtn8S+3RpLn=2xdD=ggZUvHRpet+5uhcSKMMdq0FA=MkdMg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2012-02-01 17:52:11 Re: Index with all necessary columns - Postgres vs MSSQL
Previous Message Claudio Freire 2012-02-01 03:51:03 Re: How to improve insert speed with index on text column