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