-----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----- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
