Re: Index with all necessary columns - Postgres vs MSSQL

On Wed, Feb 1, 2012 at 10:10 AM, Gudmundur Johannesson
<gudmundur(dot)johannesson(at)gmail(dot)com> wrote:
> 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?

As Merlin mentioned postgres doesn't have "covering" indexes yet. I
was wondering what explain ANALYZE of your query looks like, and what
version of pgsql you're running. It might be that we can at least get
that 23 seconds down to something closer to 1 second rather than
waiting for pg 9.2 to get here.

First try individual indexes on the two fields, and also try a two
column index on the two fields, both with id first and with date
first. Use explain analyze to see if this does any better. also look
at this wiki page and see if there's anything there that helps:
http://wiki.postgresql.org/wiki/SlowQueryQuestions Especially this
part: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems