-----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-----