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:
*) are the times in postgres stable across calls?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?
*) 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
