Search Postgresql Archives

[EDIT] Timestamp indicies not being used!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux