Re: BUG #3270: limit < 16 optimizer behaviour

This should have been asked on the performance list, not filed as a bug.
I doubt anyone will have a complete answer to your question without
EXPLAIN ANALYZE output from the query.

Have you ANALYZE'd the tables recently? Poor statistics is one possible
cause of the issue you are having.

On Fri, May 11, 2007 at 14:07:57 +0000,
Liviu Ionescu <ilgb(at)livius(dot)net> wrote:
>
> The following bug has been logged online:
>
> Bug reference: 3270
> Logged by: Liviu Ionescu
> Email address: ilgb(at)livius(dot)net
> PostgreSQL version: 8.2.4
> Operating system: Linux
> Description: limit < 16 optimizer behaviour
> Details:
>
> I have a table of about 15Mrows, and a query like this:
>
> SELECT historianid,storagedate,slotdate,status,value FROM historiandata
> JOIN rtunodes ON(historiandata.historianid=rtunodes.nodeid)
> JOIN rtus ON(rtunodes.rtuid=rtus.nodeid)
> WHERE realmid IN (1119,1422,698,1428)
> AND historianid in (2996)
> ORDER BY storagedate desc
> LIMIT 10
>
> if there are no records with the given historianid, if limit is >= 16 the
> query is quite fast, otherwise it takes forever.
>
> my current fix was to always increase the limit to 16, but, although I know
> the optimizer behaviour depends on LIMIT, I still feel this looks like a
> bug; if the resultset has no records the value of the LIMIT should not
> matter.
>
> regards,
>
> Liviu Ionescu
>
>
>
> CREATE TABLE historiandata
> (
> historianid int4 NOT NULL,
> status int2 NOT NULL DEFAULT 0,
> value float8,
> slotdate timestamptz NOT NULL,
> storagedate timestamptz NOT NULL DEFAULT now(),
> CONSTRAINT historiandata_pkey PRIMARY KEY (historianid, slotdate),
> CONSTRAINT historianid_fkey FOREIGN KEY (historianid)
> REFERENCES historians (nodeid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE RESTRICT
> )
> WITHOUT OIDS;
> ALTER TABLE historiandata OWNER TO tomcat;
>
>
> -- Index: historiandata_historianid_index
>
> -- DROP INDEX historiandata_historianid_index;
>
> CREATE INDEX historiandata_historianid_index
> ON historiandata
> USING btree
> (historianid);
>
> -- Index: historiandata_slotdate_index
>
> -- DROP INDEX historiandata_slotdate_index;
>
> CREATE INDEX historiandata_slotdate_index
> ON historiandata
> USING btree
> (slotdate);
>
> -- Index: historiandata_storagedate_index
>
> -- DROP INDEX historiandata_storagedate_index;
>
> CREATE INDEX historiandata_storagedate_index
> ON historiandata
> USING btree
> (storagedate);
>
>
> CREATE TABLE rtunodes
> (
> nodeid int4 NOT NULL,
> rtuid int4 NOT NULL,
> no_publicnodeid int4,
> name varchar(64) NOT NULL,
> isinvalid bool NOT NULL DEFAULT false,
> nodetype varchar(16),
> CONSTRAINT rtunodes_pkey PRIMARY KEY (nodeid),
> CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid)
> REFERENCES nodes (nodeid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE RESTRICT,
> CONSTRAINT rtuid_fkey FOREIGN KEY (rtuid)
> REFERENCES rtus (nodeid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE RESTRICT
> )
> WITHOUT OIDS;
> ALTER TABLE rtunodes OWNER TO tomcat;
>
>
>
> CREATE TABLE rtus
> (
> nodeid int4 NOT NULL,
> passwd varchar(10) NOT NULL,
> xml text,
> no_nextpublicnodeid int4 NOT NULL DEFAULT 1,
> rtudriverid int2,
> realmid int4 NOT NULL,
> enablegetlogin bool NOT NULL DEFAULT false,
> enablegetconfig bool NOT NULL DEFAULT false,
> businfoxml text,
> uniqueid varchar(32) NOT NULL,
> no_publicrtuid int4,
> loginname varchar(10) NOT NULL,
> protocolversion varchar(8) DEFAULT '0.0'::character varying,
> isinvalid bool DEFAULT false,
> CONSTRAINT rtus_pkey PRIMARY KEY (nodeid),
> CONSTRAINT nodeid_fkey FOREIGN KEY (nodeid)
> REFERENCES nodes (nodeid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE RESTRICT,
> CONSTRAINT realmid_fkey FOREIGN KEY (realmid)
> REFERENCES realms (nodeid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE RESTRICT,
> CONSTRAINT rtudriverid_fkey FOREIGN KEY (rtudriverid)
> REFERENCES rtudrivers (rtudriverid) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE RESTRICT,
> CONSTRAINT rtus_loginname_unique UNIQUE (loginname),
> CONSTRAINT rtus_uniqueid_unique UNIQUE (uniqueid)
> )
> WITHOUT OIDS;
> ALTER TABLE rtus OWNER TO tomcat;
>
>
> -- Index: rtus_realmid_index
>
> -- DROP INDEX rtus_realmid_index;
>
> CREATE INDEX rtus_realmid_index
> ON rtus
> USING btree
> (realmid);
>
> -- Index: rtus_rtudriverid_index
>
> -- DROP INDEX rtus_rtudriverid_index;
>
> CREATE INDEX rtus_rtudriverid_index
> ON rtus
> USING btree
> (rtudriverid);
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly