Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group