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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Thank you Alban for your considerations.
The issue has already been marked as [SOLVED] following Sam's suggestion.

And FYI the issue *was* urgent and the wolf *was* biting my leg! :]

BR,

Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam




Alban Hertroys wrote:
> 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!
>
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFKZDtl2FH5GXCfxAsRAq4BAKCz6J8+ellx1DsaXLeznV6E4z7OkACgqwjK
RbZ0c+jvNYD+vxJi2ucneCg=
=D6re
-----END PGP SIGNATURE-----