Thread: Timestamp indicies not being used!
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" 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
Pedro Doria Meunier <pdoria@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°
On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote: > 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); Based on your query, I think you want a multi-column index---probably on (id,zulu_timestamp). The problem with just having an index on either column is that it's difficult to combine them and PG hence just thinks that it will be easier to scan backwards in time looking for the first entry for the identifier you specified. BTW, if you're concerned about insert performance then the less indexes you have the better. -- Sam http://samason.me.uk/
Sam Mason <sam@samason.me.uk> wrote: > On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote: > > 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); > > Based on your query, I think you want a multi-column index---probably on > (id,zulu_timestamp). > > The problem with just having an index on either column is that it's > difficult to combine them and PG hence just thinks that it will be Since 8.1 PG can do an bitmap index scan using both indexes... 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°
On Sun, Jul 19, 2009 at 02:56:04PM +0200, Andreas Kretschmer wrote: > Sam Mason <sam@samason.me.uk> wrote: > > The problem with just having an index on either column is that it's > > difficult to combine them and PG hence just thinks that it will be > > Since 8.1 PG can do an bitmap index scan using both indexes... Yes, and with decent stats (as you pointed out) it'll know better when to do this. Even with better stats it may not help much as there could be cross-column correlations that are causing PG's estimates to be off. -- Sam http://samason.me.uk/
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Sam Mason <sam@samason.me.uk> wrote:
>> Based on your query, I think you want a multi-column index---probably on
>> (id,zulu_timestamp).
>>
>> The problem with just having an index on either column is that it's
>> difficult to combine them and PG hence just thinks that it will be
> Since 8.1 PG can do an bitmap index scan using both indexes...
... which produces an unordered result, so it's pretty useless for this
problem. I concur with Sam's suggestion.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 First of all *thank* you very much to all that replied! :) Sam's suggestion actually did the trick! I created the multi-column index and the stalling went away. Yay! Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: > Andreas Kretschmer <akretschmer@spamfence.net> writes: >> Sam Mason <sam@samason.me.uk> wrote: >>> Based on your query, I think you want a multi-column >>> index---probably on (id,zulu_timestamp). >>> >>> The problem with just having an index on either column is that >>> it's difficult to combine them and PG hence just thinks that it >>> will be > >> Since 8.1 PG can do an bitmap index scan using both indexes... > > ... which produces an unordered result, so it's pretty useless for > this problem. I concur with Sam's suggestion. > > regards, tom lane > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKY2hd2FH5GXCfxAsRAjj7AJ4lGNmNuwPUuTv6EwZ8s2faZcOASACgka8P aE4vwzcy3cIdbEyFtYWTjAw= =I5Jv -----END PGP SIGNATURE-----