Timestamp indicies not being used!

From: Pedro Doria Meunier <pdoria(at)netmadeira(dot)com>
To: Postgresql Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Timestamp indicies not being used!
Date: 2009-07-19 10:15:39
Message-ID: 4A62F24B.3010807@netmadeira.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pedro Doria Meunier 2009-07-19 10:39:51 [EDIT] Timestamp indicies not being used!
Previous Message Abraham, Danny 2009-07-19 09:35:43 Re: initdb fails on Windows with encoding=LATIN1