When I excluded the non indexed search criteria the query on aggregate used the indexes on each partition, without specifying the constraint exclusion criteria. When I added the constraint exclusion criteria to the non indexed criteria, it still used seq scans.
I ended up getting an acceptable plan by using a subquery on the indexed partition and using those results to scan for the unindexed value. On Wed, Sep 21, 2016 at 12:37 PM, Mike Broers <mbro...@gmail.com> wrote: > 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.kannan@ > 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-pa >> rtitioning.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-ow...@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. >> > >
