Thread: Bad Query Plan with Range Query
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
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
Mark Williams <mark.williams@jivesoftware.com> wrote: > explain SELECT min(messageID) FROM Message > WHERE modificationDate >= 1302627793988; > For some reason it is deciding to scan the primary key column of > the table. This results in scanning the entire table No, it scans until it finds the first row where modificationDate >= 1302627793988, at which point the scan is done because it's doing an ascending scan on what you want the min() of. You might have a clue that the first such row will be ten million rows into the scan, but the optimizer doesn't know that. It's assuming that rows which meet that condition are scattered randomly through the primary key range. It thinks that it will, on average, need to scan 1249 rows to find a match. The patch Ken referenced causes the alternative to be assigned a more accurate (and lower) cost, which tips the scales in favor of that plan -- at least for the case you've tried; but this seems to me to be another case related to the correlation of values. It's a new and different form of it, but it seems at least somewhat related. It might be a good example for those working on multi-column statistics to keep in mind. -Kevin
Thanks for the response guys. There is something else which confuses me.
If I re-write the query like this:
explain SELECT messageID FROM Message WHERE modificationDate >=
1302627793988 ORDER BY modificationDate LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.97 rows=1 width=16)
-> Index Scan using jvmssg_mdate_idx on message
(cost=0.00..3705.59 rows=1249 width=16)
Index Cond: (modificationdate >= 1302627793988::bigint)
(3 rows)
I also get a better plan. However, this is not always the case. On some
other instances we still get a sequential scan on the primary key.
On 04/15/2011 10:54 AM, Kevin Grittner wrote:
> Mark Williams<mark.williams@jivesoftware.com> wrote:
>
>> explain SELECT min(messageID) FROM Message
>> WHERE modificationDate>= 1302627793988;
>
>> For some reason it is deciding to scan the primary key column of
>> the table. This results in scanning the entire table
>
> No, it scans until it finds the first row where modificationDate>=
> 1302627793988, at which point the scan is done because it's doing an
> ascending scan on what you want the min() of. You might have a clue
> that the first such row will be ten million rows into the scan, but
> the optimizer doesn't know that. It's assuming that rows which meet
> that condition are scattered randomly through the primary key range.
> It thinks that it will, on average, need to scan 1249 rows to find a
> match.
>
> The patch Ken referenced causes the alternative to be assigned a
> more accurate (and lower) cost, which tips the scales in favor of
> that plan -- at least for the case you've tried; but this seems to
> me to be another case related to the correlation of values. It's a
> new and different form of it, but it seems at least somewhat
> related. It might be a good example for those working on
> multi-column statistics to keep in mind.
>
> -Kevin
Mark Williams <mark.williams@jivesoftware.com> wrote: > If I re-write the query like this: > > explain SELECT messageID FROM Message WHERE modificationDate >= > 1302627793988 ORDER BY modificationDate LIMIT 1; > I also get a better plan. Yeah, but it's not necessarily the same value. Do you want the minimum messageID where modificationDate >= 1302627793988 or do you want the messageID of some row (possibly of many) with the minimum modificationDate where modificationDate >= 1302627793988? Since you're asking for a logically different value with that query, it's not surprising it uses a different plan. -Kevin
Whoops, I meant this query (ordering my messageid): SELECT messageID FROM Message WHERE modificationDate>= 1302627793988 ORDER BY messageID LIMIT 1; Sometimes this gives the better plan. But not always. On 04/15/2011 11:13 AM, Kevin Grittner wrote: > Mark Williams<mark.williams@jivesoftware.com> wrote: > >> If I re-write the query like this: >> >> explain SELECT messageID FROM Message WHERE modificationDate>= >> 1302627793988 ORDER BY modificationDate LIMIT 1; > >> I also get a better plan. > > Yeah, but it's not necessarily the same value. Do you want the > minimum messageID where modificationDate>= 1302627793988 or do you > want the messageID of some row (possibly of many) with the minimum > modificationDate where modificationDate>= 1302627793988? > > Since you're asking for a logically different value with that query, > it's not surprising it uses a different plan. > > -Kevin