Re: Bad Query Plan with Range Query - Mailing list pgsql-performance

On Fri, Apr 15, 2011 at 10:17:32AM -0700, Mark Williams wrote:
> We are experiencing a problem with our query plans when using a range query
> in Postgresql 8.3. The query we are executing attempts to select the
> minimum primary key id after a certain date. Our date columns are bigint's
> holding a unix epoch representation of the date. We have an index on the
> primary key and the date column.
>
> For the following query just specified the predicate modificationDate >= ?
>
> explain SELECT min(messageID) FROM Message WHERE modificationDate >=
> 1302627793988;
>                                                QUERY PLAN
> ---------------------------------------------------------------------------------------------------------
>  Result  (cost=2640.96..2640.97 rows=1 width=0)
>    InitPlan
>      ->  Limit  (cost=0.00..2640.96 rows=1 width=8)
>            ->  Index Scan using message_pk on message
> (cost=0.00..3298561.09 rows=1249 width=8)
>                  Filter: ((messageid IS NOT NULL) AND (modificationdate >=
> 1302627793988::bigint))
> (5 rows)
>
> For some reason it is deciding to scan the primary key column of the table.
> This results in scanning the entire table which is huge (10 million
> records).
>
> However, if we specify a fake upper bound then the planner will correctly
> use the date column index:
>
> explain SELECT min(messageID) FROM Message WHERE modificationDate >=
> 1302627793988 and modificationDate < 9999999999999999;
>                                                      QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
>  Aggregate  (cost=9.64..9.65 rows=1 width=8)
>    ->  Index Scan using jvmssg_mdate_idx on message  (cost=0.00..9.64
> rows=1 width=8)
>          Index Cond: ((modificationdate >= 1302627793988::bigint) AND
> (modificationdate < 9999999999999999::bigint))
> (3 rows)
>
> We have carried out all the usual maintenance tasks. We have increase the
> statistics_target on both indexes to the maximum (1000) and performed a
> vacuum analyze on the table. Our resource configurations are very good
> since this is our production server.
>
> Interestingly this does not appear to happen with exactly the same database
> when using 8.4. Instead we get the correct plan without having to add the
> upper bound.
>
> Here is the full description of the the table. It contains upwards of 10
> million rows.
>
>               Table "public.message"
>       Column      |          Type          | Modifiers
> ------------------+------------------------+-----------
>  messageid        | bigint                 | not null
>  parentmessageid  | bigint                 |
>  threadid         | bigint                 | not null
>  containertype    | integer                | not null
>  containerid      | bigint                 | not null
>  userid           | bigint                 |
>  subject          | character varying(255) |
>  body             | text                   |
>  modvalue         | integer                | not null
>  rewardpoints     | integer                | not null
>  creationdate     | bigint                 | not null
>  modificationdate | bigint                 | not null
>  status           | integer                | not null
> Indexes:
>     "message_pk" PRIMARY KEY, btree (messageid)
>     "jvmssg_cdate_idx" btree (creationdate)
>     "jvmssg_cidctmd_idx" btree (containerid, containertype,
> modificationdate)
>     "jvmssg_mdate_idx" btree (modificationdate)
>     "jvmssg_mdvle_idx" btree (modvalue)
>     "jvmssg_prntid_idx" btree (parentmessageid)
>     "jvmssg_thrd_idx" btree (threadid)
>     "jvmssg_usrid_idx" btree (userid)
> Referenced by:
>     TABLE "answer" CONSTRAINT "answer_mid_fk" FOREIGN KEY (messageid)
> REFERENCES message(messageid)
>     TABLE "messageprop" CONSTRAINT "jmp_msgid_fk" FOREIGN KEY (messageid)
> REFERENCES message(messageid)
>
>
> Any insight into this would be greatly appreciated. We are not able to
> upgrade our databases to 8.4. We are reluctant to re-write all our range
> queries if possible.
>
>
> -m
>

Here is the fix that was added to 8.4+:

http://archives.postgresql.org/pgsql-committers/2010-01/msg00021.php

I think you are stuck with one of those options so if upgrading
is not available, then re-writing the range queries wins by a landslide. :)

Regards,
Ken