Re: Data comparison SQL in PG 8.2.9

Phoenix Kiula wrote:

>                                 Table "public.users"
>           Column         |            Type             |
> Modifiers
>   -----------------------+-----------------------------+------------------------------
>       modify_date           | timestamp without time zone |
>   ....
>   Indexes:
>       "new_idx_modify_date" btree (modify_date) WITH (fillfactor=75)
>
>
> Any ideas why? I ran an explain analyze and this is the output:

> Another weird thing is that a query that has ">" a certain
> modify_date, even just yesterday as the date condition, takes a LONG
> time and is almost unusable.

Have you tried REINDEX? VACUUM ANALYZE since the update?
Not sure the security fix relating to indexes in 8.2.6 affects you.

Did the suggestion of adding the type cast affect the query?
If so then maybe the planner is now running the type conversion per row
instead of a one off from the constant in the select.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz