Re: [EDIT] Timestamp indicies not being used! - Mailing list pgsql-general

On 19 Jul 2009, at 12:39, Pedro Doria Meunier wrote:

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


 From this plan it appears the planner statistics aren't up to date or
the statistics size on the timestamp column is too small, as the
expected number of rows (44192) doesn't match the actual number (0) at
all. Some experimenting with ANALYSE and column statistics should tell
whether this is indeed the problem.
That said statistics are most useful for common cases, they're usually
not very accurate for exceptions so playing around with those may not
give the desired results.

What happens in above query plan is that the planner scans a large
part of rows referred to from the timestamp index (namely all those
before the specified timestamp) to find any rows matching the id.
There are a few things you could do about that:

1) You could specify a lower boundary for the timestamps. The way
you're going about it the longer your application runs the more rows
will match your zulu_timestamp < '2009-07-10 15:24:45+01' expression.
It seems likely that you know that the timestamp is at least in
2009-10 for example, reducing the matching rows by a lot once your
application is running for several months.

2) You could define a multi-column index instead of two separate
indexes. Which column should be first depends on which column you
query on most frequently, but I expect it to be (id, zulu_timestamp).
With such an index the matching rows are known to be in the index and
thus looking them up should be a lot faster.

Finally, sending every message as urgent is not going to help you.
It's like this: http://en.wikipedia.org/wiki/The_boy_who_cried_wolf

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a6437be10131991414558!