Thread: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table

The following bug has been logged online:

Bug reference:      4721
Logged by:          Eric Thompson
Email address:      eric.thompson@salliemae.com
PostgreSQL version: 8.3.7
Operating system:   RedHat ES 5.2
Description:        All sub-tables incorrectly included in search plan for
partitioned table
Details:

Greetings,

First, let me thank you for developing and maintaining PostgreSQL.   I
recently noticed search plans for partitioned tables are no longer excluding
partitions that should be excluded with constraint_exclusion turned on.    I
have included the steps needed to reproduce the problem.  Please let me know
if you need any additional information.

Thanks again for your time,
Eric

--------------------------------------

$ uname -a
Linux 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64
GNU/Linux
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)
$ createdb test
$ psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# select version();
                                                  version

----------------------------------------------------------------------------
-------------------------------
 PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

test=# \i db.dump
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
test=# \dt
             List of relations
 Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
 public | stats          | table | postgres
 public | stats_y2008m03 | table | postgres
 public | stats_y2008m04 | table | postgres
 public | stats_y2008m05 | table | postgres
 public | stats_y2008m06 | table | postgres
(5 rows)

test=# show constraint_exclusion ;
 constraint_exclusion
----------------------
 on
(1 row)

test=# -- expect to see only sub-table y2008m04 included in the search plan
for this query,
test=# -- instead all the sub-tables are in the search plan
test=# explain select * from stats where time='4/12/2008 12:12';
                                     QUERY PLAN

----------------------------------------------------------------------------
---------
 Result  (cost=0.00..67.50 rows=5 width=256)
   ->  Append  (cost=0.00..67.50 rows=5 width=256)
         ->  Seq Scan on stats  (cost=0.00..13.50 rows=1 width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
         ->  Seq Scan on stats_y2008m03 stats  (cost=0.00..13.50 rows=1
width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
         ->  Seq Scan on stats_y2008m04 stats  (cost=0.00..13.50 rows=1
width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
         ->  Seq Scan on stats_y2008m05 stats  (cost=0.00..13.50 rows=1
width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
         ->  Seq Scan on stats_y2008m06 stats  (cost=0.00..13.50 rows=1
width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
(12 rows)

test=# -- remove any irrelevant constraint from the master table, and now
the date partitioning works
test=# -- (only y2008m04 is included in the search plan for this query)
test=# alter table stats drop constraint "stats_% hit_check";
ALTER TABLE
test=# explain select * from stats where time='4/12/2008 12:12';
                                     QUERY PLAN

----------------------------------------------------------------------------
---------
 Result  (cost=0.00..27.00 rows=2 width=256)
   ->  Append  (cost=0.00..27.00 rows=2 width=256)
         ->  Seq Scan on stats  (cost=0.00..13.50 rows=1 width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
         ->  Seq Scan on stats_y2008m04 stats  (cost=0.00..13.50 rows=1
width=256)
               Filter: ("time" = '2008-04-12 12:12:00'::timestamp without
time zone)
(6 rows)

test=#

--------------------------------
contents of   db.dump:

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_with_oids = false;

CREATE TABLE stats (
    id0 text NOT NULL,
    a_id integer NOT NULL,
    "time" timestamp without time zone NOT NULL,
    "ios per sec" real NOT NULL,
    "reads per sec" real NOT NULL,
    "writes per sec" real NOT NULL,
    "hits per sec" real NOT NULL,
    "read hits per sec" real NOT NULL,
    "write hits per sec" real NOT NULL,
    "seq reads per sec" real NOT NULL,
    "seq read hits per sec" real NOT NULL,
    "seq writes per sec" real NOT NULL,
    "Kbytes read per sec" real NOT NULL,
    "Kbytes written per sec" real NOT NULL,
    "DA read requests per sec" real NOT NULL,
    "DA write requests per sec" real NOT NULL,
    "DA prefetched tracks per sec" real NOT NULL,
    "DA prefetched tracks used per sec" real NOT NULL,
    "DA Kbytes read per sec" real NOT NULL,
    "DA Kbytes written per sec" real NOT NULL,
    "write pending count" real NOT NULL,
    "max write pending threshold" real NOT NULL,
    "sampled RDF write waits per sec" real NOT NULL,
    "sampled average read time (ms)" real NOT NULL,
    "sampled average write time (ms)" real NOT NULL,
    "sampled average read miss time (ms)" real NOT NULL,
    "sampled average WP disconnect time (ms)" real NOT NULL,
    "total DA req per sec" real NOT NULL,
    "total reads per sec" real NOT NULL,
    "total read hits per sec" real NOT NULL,
    "total read misses per sec" real NOT NULL,
    "total ios per sec" real NOT NULL,
    "total hits per sec" real NOT NULL,
    "total misses per sec" real NOT NULL,
    "write misses per sec" real NOT NULL,
    "read misses per sec" real NOT NULL,
    "seq ios per sec" real NOT NULL,
    "% random read hit" real NOT NULL,
    "% random read miss" real NOT NULL,
    "% sequential read" real NOT NULL,
    "% write" real NOT NULL,
    "% read" real NOT NULL,
    "% hit" real NOT NULL,
    "% miss" real NOT NULL,
    "% read hit" real NOT NULL,
    "% write hit" real NOT NULL,
    "% read miss" real NOT NULL,
    "% write miss" real NOT NULL,
    "% sequential io" real NOT NULL,
    "% sequential writes" real NOT NULL,
    "HA Kbytes transferred per sec" real NOT NULL,
    "average read size in Kbytes" real NOT NULL,
    "average write size in Kbytes" real NOT NULL,
    "average io size in Kbytes" real NOT NULL,
    "DA Kbytes transferred per sec" real NOT NULL,
    "System bus Kbytes per sec" real NOT NULL,
    CONSTRAINT "stats_% hit_check" CHECK (("% hit" >= (0)::double
precision)),
    CONSTRAINT "stats_% miss_check" CHECK (("% miss" >= (0)::double
precision)),
    CONSTRAINT "stats_% random read hit_check" CHECK (("% random read hit"
>= (0)::double precision)),
    CONSTRAINT "stats_% random read miss_check" CHECK (("% random read miss"
>= (0)::double precision)),
    CONSTRAINT "stats_% read hit_check" CHECK (("% read hit" >= (0)::double
precision)),
    CONSTRAINT "stats_% read miss_check" CHECK (("% read miss" >=
(0)::double precision)),
    CONSTRAINT "stats_% read_check" CHECK (("% read" >= (0)::double
precision)),
    CONSTRAINT "stats_% sequential io_check" CHECK (("% sequential io" >=
(0)::double precision)),
    CONSTRAINT "stats_% sequential read_check" CHECK (("% sequential read"
>= (0)::double precision)),
    CONSTRAINT "stats_% sequential writes_check" CHECK (("% sequential
writes" >= (0)::double precision)),
    CONSTRAINT "stats_% write hit_check" CHECK (("% write hit" >=
(0)::double precision)),
    CONSTRAINT "stats_% write miss_check" CHECK (("% write miss" >=
(0)::double precision)),
    CONSTRAINT "stats_% write_check" CHECK (("% write" >= (0)::double
precision)),
    CONSTRAINT "stats_DA Kbytes read per sec_check" CHECK (("DA Kbytes read
per sec" >= (0)::double precision)),
    CONSTRAINT "stats_DA Kbytes transferred per sec_check" CHECK (("DA
Kbytes transferred per sec" >= (0)::double precision)),
    CONSTRAINT "stats_DA Kbytes written per sec_check" CHECK (("DA Kbytes
written per sec" >= (0)::double precision)),
    CONSTRAINT "stats_DA prefetched tracks per sec_check" CHECK (("DA
prefetched tracks per sec" >= (0)::double precision)),
    CONSTRAINT "stats_DA prefetched tracks used per sec_check" CHECK (("DA
prefetched tracks used per sec" >= (0)::double precision)),
    CONSTRAINT "stats_DA read requests per sec_check" CHECK (("DA read
requests per sec" >= (0)::double precision)),
    CONSTRAINT "stats_DA write requests per sec_check" CHECK (("DA write
requests per sec" >= (0)::double precision)),
    CONSTRAINT "stats_HA Kbytes transferred per sec_check" CHECK (("HA
Kbytes transferred per sec" >= (0)::double precision)),
    CONSTRAINT "stats_Kbytes read per sec_check" CHECK (("Kbytes read per
sec" >= (0)::double precision)),
    CONSTRAINT "stats_Kbytes written per sec_check" CHECK (("Kbytes written
per sec" >= (0)::double precision)),
    CONSTRAINT "stats_System bus Kbytes per sec_check" CHECK (("System bus
Kbytes per sec" >= (0)::double precision)),
    CONSTRAINT "stats_average io size in Kbytes_check" CHECK (("average io
size in Kbytes" >= (0)::double precision)),
    CONSTRAINT "stats_average read size in Kbytes_check" CHECK (("average
read size in Kbytes" >= (0)::double precision)),
    CONSTRAINT "stats_average write size in Kbytes_check" CHECK (("average
write size in Kbytes" >= (0)::double precision)),
    CONSTRAINT "stats_hits per sec_check" CHECK (("hits per sec" >=
(0)::double precision)),
    CONSTRAINT "stats_ios per sec_check" CHECK (("ios per sec" >=
(0)::double precision)),
    CONSTRAINT "stats_max write pending threshold_check" CHECK (("max write
pending threshold" >= (0)::double precision)),
    CONSTRAINT "stats_read hits per sec_check" CHECK (("read hits per sec"
>= (0)::double precision)),
    CONSTRAINT "stats_read misses per sec_check" CHECK (("read misses per
sec" >= (0)::double precision)),
    CONSTRAINT "stats_reads per sec_check" CHECK (("reads per sec" >=
(0)::double precision)),
    CONSTRAINT "stats_sampled RDF write waits per sec_check" CHECK
(("sampled RDF write waits per sec" >= (0)::double precision)),
    CONSTRAINT "stats_sampled average WP disconnect time (m_check" CHECK
(("sampled average WP disconnect time (ms)" >= (0)::double precision)),
    CONSTRAINT "stats_sampled average read miss time (ms)_check" CHECK
(("sampled average read miss time (ms)" >= (0)::double precision)),
    CONSTRAINT "stats_sampled average read time (ms)_check" CHECK (("sampled
average read time (ms)" >= (0)::double precision)),
    CONSTRAINT "stats_sampled average write time (ms)_check" CHECK
(("sampled average write time (ms)" >= (0)::double precision)),
    CONSTRAINT "stats_seq ios per sec_check" CHECK (("seq ios per sec" >=
(0)::double precision)),
    CONSTRAINT "stats_seq read hits per sec_check" CHECK (("seq read hits
per sec" >= (0)::double precision)),
    CONSTRAINT "stats_seq reads per sec_check" CHECK (("seq reads per sec"
>= (0)::double precision))
);


ALTER TABLE public.stats OWNER TO postgres;

--
-- Name: stats_y2008m03; Type: TABLE; Schema: public; Owner: postgres;
Tablespace: data02
--

CREATE TABLE stats_y2008m03 (CONSTRAINT stats_y2008m03_time_check CHECK
(((date_trunc('day'::text, "time") >= '2008-03-01 00:00:00'::timestamp
without time zone) AND (date_trunc('day'::text, "time") < '2008-04-01
00:00:00'::timestamp without time zone))),
    CONSTRAINT stats_y2008m03_time_check1 CHECK ((("time" >= '2008-03-01
00:00:00'::timestamp without time zone) AND ("time" < '2008-04-01
00:00:00'::timestamp without time zone)))
)
INHERITS (stats);


ALTER TABLE public.stats_y2008m03 OWNER TO postgres;

--
-- Name: stats_y2008m04; Type: TABLE; Schema: public; Owner: postgres;
Tablespace: data02
--

CREATE TABLE stats_y2008m04 (CONSTRAINT stats_y2008m04_time_check CHECK
(((date_trunc('day'::text, "time") >= '2008-04-01 00:00:00'::timestamp
without time zone) AND (date_trunc('day'::text, "time") < '2008-05-01
00:00:00'::timestamp without time zone))),
    CONSTRAINT stats_y2008m04_time_check1 CHECK ((("time" >= '2008-04-01
00:00:00'::timestamp without time zone) AND ("time" < '2008-05-01
00:00:00'::timestamp without time zone)))
)
INHERITS (stats);


ALTER TABLE public.stats_y2008m04 OWNER TO postgres;

--
-- Name: stats_y2008m05; Type: TABLE; Schema: public; Owner: postgres;
Tablespace: data02
--

CREATE TABLE stats_y2008m05 (CONSTRAINT stats_y2008m05_time_check CHECK
(((date_trunc('day'::text, "time") >= '2008-05-01 00:00:00'::timestamp
without time zone) AND (date_trunc('day'::text, "time") < '2008-06-01
00:00:00'::timestamp without time zone))),
    CONSTRAINT stats_y2008m05_time_check1 CHECK ((("time" >= '2008-05-01
00:00:00'::timestamp without time zone) AND ("time" < '2008-06-01
00:00:00'::timestamp without time zone)))
)
INHERITS (stats);


ALTER TABLE public.stats_y2008m05 OWNER TO postgres;

--
-- Name: stats_y2008m06; Type: TABLE; Schema: public; Owner: postgres;
Tablespace: data02
--

CREATE TABLE stats_y2008m06 (CONSTRAINT stats_y2008m06_time_check CHECK
(((date_trunc('day'::text, "time") >= '2008-06-01 00:00:00'::timestamp
without time zone) AND (date_trunc('day'::text, "time") < '2008-07-01
00:00:00'::timestamp without time zone))),
    CONSTRAINT stats_y2008m06_time_check1 CHECK ((("time" >= '2008-06-01
00:00:00'::timestamp without time zone) AND ("time" < '2008-07-01
00:00:00'::timestamp without time zone)))
)
INHERITS (stats);


ALTER TABLE public.stats_y2008m06 OWNER TO postgres;

"Eric Thompson" <eric.thompson@salliemae.com> writes:
> test=# -- remove any irrelevant constraint from the master table, and now
> the date partitioning works

Hmm.  Tracing through this, it seems your child tables have exactly 101
separate constraint clauses; removing one from the parent table gets it
down to 100.  Which is where the cutoff installed by this patch is:

http://archives.postgresql.org/pgsql-committers/2008-11/msg00146.php

That patch was in response to this complaint:

http://archives.postgresql.org/pgsql-general/2008-11/msg00446.php

I'm not entirely sure about a better approach; just moving the cutoff
around doesn't seem like it will do anything except change who's
complaining...

            regards, tom lane

Interesting...  it looks like there is a balance between CPU cycles and dis=
k I/O.  I set the MAX_BRANCHES_TO_TEST to 120 and recompiled, so for me eve=
rything is fast again.   I do not know everything involved, but if there wa=
s a way to flag the constraints used for partitioning and always check thos=
e to avoid scanning child tables, that may help.  Thank you for the quick f=
eedback, and I am happy that I could achieve a quick resolution.

Thanks again,
Eric


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Sent: Saturday, March 21, 2009 1:44 AM
To: Thompson, Eric
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4721: All sub-tables incorrectly included in searc=
h plan for partitioned table=20

"Eric Thompson" <eric.thompson@salliemae.com> writes:
> test=3D# -- remove any irrelevant constraint from the master table, and n=
ow
> the date partitioning works=20

Hmm.  Tracing through this, it seems your child tables have exactly 101
separate constraint clauses; removing one from the parent table gets it
down to 100.  Which is where the cutoff installed by this patch is:

http://archives.postgresql.org/pgsql-committers/2008-11/msg00146.php

That patch was in response to this complaint:

http://archives.postgresql.org/pgsql-general/2008-11/msg00446.php

I'm not entirely sure about a better approach; just moving the cutoff
around doesn't seem like it will do anything except change who's
complaining...

            regards, tom lane

This E-Mail has been scanned for viruses.

I wrote:
> "Eric Thompson" <eric.thompson@salliemae.com> writes:
>> test=# -- remove any irrelevant constraint from the master table, and now
>> the date partitioning works

> Hmm.  Tracing through this, it seems your child tables have exactly 101
> separate constraint clauses; removing one from the parent table gets it
> down to 100.  Which is where the cutoff installed by this patch is:

> http://archives.postgresql.org/pgsql-committers/2008-11/msg00146.php

I've partially reverted that patch:

http://archives.postgresql.org/pgsql-committers/2009-05/msg00202.php

so the next 8.3.x release should behave as you're expecting.

            regards, tom lane