Thanks for your response - Is 'selectively choosing what partition' different than utilizing each partitions index when scanning each partition? To clarify, I expect to find results in each partition, but to have postgres use each partitions index instead of full table scans. It seems redundant to add a where clauses to match each exclusion criteria but i will try that and report back - thank you for the suggestion.
On Wed, Sep 21, 2016 at 12:15 PM, Ganesh Kannan < ganesh.kan...@weatheranalytics.com> wrote: > Postgres does not have capability to selectively choose child tables > unless the query's "WHERE" clause is simple, and it matches (exactly) the > CHECK constraint definition. I have resolved similar issue by explicitly > adding check constraint expression in every SQL against the master table. > This is also determined by the constraint_exclusion setting value. Check > the manual (9.5): https://www.postgresql.org/docs/current/static/ddl- > partitioning.html. > > > I would try tweaking WHERE clause to match Check constraint definition. > Global partitioning index (like in Oracle) would help, but its just my wish. > > > > Regards, > Ganesh Kannan > > > > ------------------------------ > *From:* pgsql-performance-ow...@postgresql.org <pgsql-performance-owner@ > postgresql.org> on behalf of Mike Broers <mbro...@gmail.com> > *Sent:* Wednesday, September 21, 2016 12:53 PM > *To:* pgsql-performance@postgresql.org > *Subject:* [PERFORM] query against single partition uses index, against > master table does seq scan > > Hello, I am curious about the performance of queries against a master > table that seem to do seq scans on each child table. When the same query > is issued at a partition directly it uses the partition index and is very > fast. > > The partition constraint is in the query criteria. We have non > overlapping check constraints and constraint exclusion is set to partition. > > Here is the master table > Column Type > Modifiers > aggregate_id bigint not null default > nextval('seq_aggregate'::regclass) > landing_id integer not null > client_program_id integer > sequence_number bigint > start_datetime timestamp without time zone not null > end_datetime timestamp without time zone not null > body jsonb not null > client_parsing_status_code character(1) > validation_status_code character(1) > client_parsing_datetime timestamp without time zone > validation_datetime timestamp without time zone > latest_flag_datetime timestamp without time zone > latest_flag boolean not null > Indexes: > "pk_aggregate" PRIMARY KEY, btree (aggregate_id) > "ix_aggregate_landing_id_aggregate_id_parsing_status" btree > (landing_id, aggregate_id, client_parsing_status_code) > "ix_aggregate_landing_id_start_datetime" btree (landing_id, > start_datetime) > "ix_aggregate_latest_flag" btree (latest_flag_datetime) WHERE > latest_flag = false > "ix_aggregate_validation_status_code" btree (validation_datetime) > WHERE validation_status_code = 'P'::bpchar AND latest_flag = true > Check constraints: > "ck_aggregate_client_parsing_status_code" CHECK > (client_parsing_status_code IS NULL OR (client_parsing_status_code = ANY > (ARRAY['P'::bpchar, 'F'::bpchar, 'I'::bpchar]))) > "ck_aggregate_validation_status_code" CHECK (validation_status_code > IS NULL OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar, > 'I'::bpchar]))) > Foreign-key constraints: > "fk_aggregate_client_program" FOREIGN KEY (client_program_id) > REFERENCES client_program(client_program_id) > "fk_aggregate_landing" FOREIGN KEY (landing_id) REFERENCES > landing(landing_id) > Number of child tables: 17 (Use \d+ to list them.) > > and here is a child table showing a check constraint > Table "stage.aggregate__00007223" > Column Type > Modifiers > ────────────────────────── ─────────────────────────── > aggregate_id bigint not null default > nextval('seq_aggregate'::regclass) > landing_id integer not null > client_program_id integer > sequence_number bigint > start_datetime timestamp without time zone not null > end_datetime timestamp without time zone not null > body jsonb not null > client_parsing_status_code character(1) > validation_status_code character(1) > client_parsing_datetime timestamp without time zone > validation_datetime timestamp without time zone > latest_flag_datetime timestamp without time zone > latest_flag boolean not null > Indexes: > "pk_aggregate__00007223" PRIMARY KEY, btree (aggregate_id), tablespace > "archive" > "ix_aggregate__00007223_landing_id_aggregate_id_parsing_status" btree > (landing_id, aggregate_id, client_parsing_status_code), tablespace "archive" > "ix_aggregate__00007223_landing_id_start_datetime" btree (landing_id, > start_datetime), tablespace "archive" > "ix_aggregate__00007223_latest_flag" btree (latest_flag_datetime) > WHERE latest_flag = false, tablespace "archive" > "ix_aggregate__00007223_validation_status_code" btree > (validation_datetime) WHERE validation_status_code = 'P'::bpchar AND > latest_flag = true, tablespace "archive" > Check constraints: > "ck_aggregate__00007223_landing_id" CHECK (landing_id >= 7223 AND > landing_id < 9503) > "ck_aggregate_client_parsing_status_code" CHECK > (client_parsing_status_code IS NULL OR (client_parsing_status_code = ANY > (ARRAY['P'::bpchar, 'F'::bpchar, 'I'::bpchar]))) > "ck_aggregate_validation_status_code" CHECK (validation_status_code > IS NULL OR (validation_status_code = ANY (ARRAY['P'::bpchar, 'F'::bpchar, > 'I'::bpchar]))) > Inherits: aggregate > Tablespace: "archive" > > Here is an example of the query explain plan against the master table: > > select landing_id from landing L > where exists > ( > select landing_id > from stage.aggregate A > WHERE (A.body#>>'{Cost}')::BIGINT >= 1000000000 > and L.landing_id = A.Landing_id > ) > and L.source_id = 36 > > > Hash Join (cost=59793745.91..59793775.14 rows=28 width=4) > Hash Cond: (a.landing_id = l.landing_id) > -> HashAggregate (cost=59792700.41..59792721.46 rows=2105 width=4) > Group Key: a.landing_id > -> Append (cost=0.00..59481729.32 rows=124388438 width=4) > -> Seq Scan on aggregate a (cost=0.00..0.00 rows=1 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00000000 a_1 > (cost=0.00..1430331.50 rows=2105558 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00000470 a_2 > (cost=0.00..74082.10 rows=247002 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00001435 a_3 > (cost=0.00..8174909.44 rows=17610357 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00001685 a_4 > (cost=0.00..11011311.44 rows=23516624 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00003836 a_5 > (cost=0.00..5833050.44 rows=13102557 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00005638 a_6 > (cost=0.00..5950768.16 rows=12342003 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00007223 a_7 > (cost=0.00..6561806.24 rows=13203237 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00009503 a_8 > (cost=0.00..5420961.64 rows=10931794 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00011162 a_9 > (cost=0.00..4262902.64 rows=8560011 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00012707 a_10 > (cost=0.00..4216271.28 rows=9077921 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00014695 a_11 > (cost=0.00..3441205.72 rows=7674495 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00016457 a_12 > (cost=0.00..688010.74 rows=1509212 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00016805 a_13 > (cost=0.00..145219.14 rows=311402 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00016871 a_14 (cost=0.00..21.40 > rows=190 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00016874 a_15 > (cost=0.00..478011.62 rows=1031110 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00017048 a_16 (cost=0.00..21.40 > rows=190 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Seq Scan on aggregate__00017049 a_17 > (cost=0.00..1792844.42 rows=3164774 width=4) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= > 1000000000) > -> Hash (cost=1042.69..1042.69 rows=225 width=4) > -> Seq Scan on landing l (cost=0.00..1042.69 rows=225 width=4) > Filter: (source_id = 36) > > And here is an example of the query using the index when ran against a > partition directly > > select landing_id from landing L > where exists > ( > select landing_id > from stage.aggregate__00007223 A > WHERE (A.body#>>'{Cost}')::BIGINT >= 1000000000 > and L.landing_id = A.Landing_id > ) > and L.source_id = 36 > > Nested Loop Semi Join (cost=0.56..3454.75 rows=5 width=4) > -> Seq Scan on landing l (cost=0.00..1042.69 rows=225 width=4) > Filter: (source_id = 36) > -> Index Scan using ix_aggregate__00007223_landing_id_start_datetime > on aggregate__00007223 a (cost=0.56..359345.74 rows=36173 width=4) > Index Cond: (landing_id = l.landing_id) > Filter: (((body #>> '{Cost}'::text[]))::bigint >= 1000000000) > > > The parent table never had rows, and pg_class had relpages=0. I saw a > suggestion in a different thread about updating this value to greater than > 0 so I tried that but didnt get a different plan. We have > autovacuum/analyze enabled and also run nightly vacuum/analyze on the > database to keep stats up to date. > > I'm new to troubleshooting partition query performance and not sure what I > am missing here. Any advice is appreciated. >