Pedro Doria Meunier <pdo...@netmadeira.com> wrote: > Hi All, > > I *really* need some help with this one... > > I have a table ~18M rows with a 'timestamp with time zone' column. It's > indexed thus: > > CREATE INDEX my_table_timestamp_idx > ON my_table > USING btree > (zulu_timestamp); > > whenever I issue a command like: > SELECT speed, digital_input_1, digital_input_2, digital_input_3, > digital_input_4, priority FROM my_table WHERE id='123456789012345' > AND zulu_timestamp < '2009-07-10 15:24:45+01' > ORDER BY zulu_timestamp DESC LIMIT 1 > > it takes FOREVER in instances where there's only 1 row or 0 rows in the > table > > the column id is also indexed. > > this is the query plan: > > "Limit (cost=0.00..83.88 rows=1 width=20) (actual > time=810784.212..810784.212 rows=0 loops=1)" > " -> Index Scan Backward using my_table_timestamp_idx on my_table > (cost=0.00..3706639.95 rows=44192 width=20) (actual > time=810784.210..810784.210 rows=0 loops=1)" > " Index Cond: (zulu_timestamp < '2009-07-10 > 15:24:45+01'::timestamp with time zone)" > " Filter: (id = '123456789012345'::bpchar)" > "Total runtime: 810808.298 ms"
There is a BIG difference between estimated and real rows (rows=44192 vs. rows=0. Try to increase the statistics for this column (and of course, run analyse for this table) Which PG-Version? And show us the table-definition (for the id-column). Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
