Thread: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
From
"Eric Thompson"
Date:
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;
Re: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
From
Tom Lane
Date:
"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
Re: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
From
"Thompson, Eric"
Date:
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.
Re: BUG #4721: All sub-tables incorrectly included in search plan for partitioned table
From
Tom Lane
Date:
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