-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Thank you Alban for your considerations. The issue has already been marked as [SOLVED] following Sam's suggestion.
And FYI the issue *was* urgent and the wolf *was* biting my leg! :] BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Alban Hertroys wrote: > On 19 Jul 2009, at 12:39, Pedro Doria Meunier wrote: > >> Hash: SHA1 >> >> 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 <EDIT>for a date equal to the one being compared</EDIT> >> >> 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" > > > From this plan it appears the planner statistics aren't up to date > or the statistics size on the timestamp column is too small, as the > expected number of rows (44192) doesn't match the actual number (0) > at all. Some experimenting with ANALYSE and column statistics should > tell whether this is indeed the problem. > That said statistics are most useful for common cases, they're > usually not very accurate for exceptions so playing around with > those may not give the desired results. > > What happens in above query plan is that the planner scans a large > part of rows referred to from the timestamp index (namely all those > before the specified timestamp) to find any rows matching the id. > There are a few things you could do about that: > > 1) You could specify a lower boundary for the timestamps. The way > you're going about it the longer your application runs the more rows > will match your zulu_timestamp < '2009-07-10 15:24:45+01' > expression. It seems likely that you know that the timestamp is at > least in 2009-10 for example, reducing the matching rows by a lot > once your application is running for several months. > > 2) You could define a multi-column index instead of two separate > indexes. Which column should be first depends on which column you > query on most frequently, but I expect it to be (id, > zulu_timestamp). With such an index the matching rows are known to > be in the index and thus looking them up should be a lot faster. > > Finally, sending every message as urgent is not going to help you. > It's like this: http://en.wikipedia.org/wiki/The_boy_who_cried_wolf > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:737,4a6437be10131991414558! > > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKZDtl2FH5GXCfxAsRAq4BAKCz6J8+ellx1DsaXLeznV6E4z7OkACgqwjK RbZ0c+jvNYD+vxJi2ucneCg= =D6re -----END PGP SIGNATURE----- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
