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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
