index usage on timestamp without time zone

From: Gerhard Hintermayer <gerhard(dot)hintermayer(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: index usage on timestamp without time zone
Date: 2011-04-28 14:38:47
Message-ID: BANLkTi=mTR6Ly1JKD3GhT5LaxFGo=NYPXw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

I'm trying to make use of indices for following table
auftrag_l1

a_nr | integer
ts | timestamp without time zone
and 10-15 other columns

I do have indices
"idx_auftrag_l1_anr" btree (a_nr)
"idx_auftrag_l1_ts" btree (ts)

when I query the table by

explain analyze select * from auftrag_l1 where ts>'01-01-2009'::timestamp
without time zone and ts <' 31-12-2010'::timestamp without time zone;

I get the following query plan

Seq Scan on auftrag_l1 (cost=0.00..334.04 rows=4885 width=89) (actual
time=0.097..4.172 rows=4893 loops=1)
Filter: ((ts > 'Thu 01 Jan 00:00:00 2009'::timestamp without time zone)
AND (ts < 'Fri 31 Dec 00:00:00 2010'::timestamp without time zone))
Total runtime: 4.933 ms
(3 rows)

Is there a way to make postgres use the index on the timestamp column ? The
timing is from a test table, production tables have a lot more rows.
Interesting, that the #rows decrease, when I shorten the timespan, so the
number of rows scanned is influenced by the selected timespan, but postgres
still does seq. scans.

Gerhard

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2011-04-28 15:07:19 Re: index usage on timestamp without time zone
Previous Message Kevin Grittner 2011-04-28 14:37:59 Re: BUG #5995: connection pooling not working