Обсуждение: index usage on timestamp without time zone
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
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
Gerhard Hintermayer <gerhard.hintermayer@gmail.com> wrote: > 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. It's a mistake to assume that the plan you get on a small table will resemble the plan you will get on a big table. If you want to know what the plan will be on the big table, at least run an EXPLAIN of the query on that big table. -Kevin