Re: Index with all necessary columns - Postgres vs MSSQL

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

 



Hi,

Here are the answers to your questions:
1) I change the select statement so I am refering to 1 day at a time.  In that case the response time is similar.  Basically, the data is not in cache when I do that and the response time is about 23 seconds.

2) The list of IDs is provided by the middle layer and represents a logical group.
btw: There are about 360 devices there.  The distribution of dtStamp is approx 200.000.000 rows / 360 devices / (4 months) which gives approx 4600 dtStamp values per device per day.

3) The query takes 23 sec vs 1 sec or lower in mssql.

We never update/delete and therefore the data is alway correct in the index (never dirty).  Therefore, Postgres could have used the data in it.

I started to add columns into indexes in Oracle for approx 15 years ago and it was a brilliant discovery.  This looks like a show stopper for me but I will

Thanks,
   - Gummi

On Wed, Feb 1, 2012 at 5:52 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Wed, Feb 1, 2012 at 11:10 AM, Gudmundur Johannesson
<gudmundur.johannesson@xxxxxxxxx> 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?

*) are the times in postgres stable across calls?
*) where is the 'id list' coming from?
*) how long does this query take?

SELECT count(1) FROM test WHERE id = 202 AND AND dtstamp between
'2011-10-19 08:00:00'::timestamp  and '2011-10-19
16:00:00'::timestamp; ?

The feature you're looking for in postgres is called 'index only
scans' and an 9.2 will contain an implementation of that feature (see:
http://rhaas.blogspot.com/2011/10/index-only-scans-weve-got-em.html).

merlin


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

  Powered by Linux