BUG #3270: limit < 16 optimizer behaviour - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3270
Logged by:          Liviu Ionescu
Email address:      ilgb@livius.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);