Обсуждение: [EDIT] Timestamp indicies not being used!
-----BEGIN PGP SIGNED MESSAGE----- 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" Since there are hundreds of devices delivering their data payload to the my_table these queries effectively block other and take postgresql to a screeching halt ... :-( Could someone PLEASE tell me how can I solve this? Thanks in advance, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKYvfr2FH5GXCfxAsRAvvxAKCYDq+diq1IhXVFDaB/N3RLR+VNUACfWDnR H7SXWGXR0yAoXezLpKTJddk= =XH5b -----END PGP SIGNATURE-----
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 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-----