query against single partition uses index, against master table does seq scan

Поиск

Список

Период

Сортировка

От	Mike Broers
Тема	query against single partition uses index, against master table does seq scan
Дата	21 сентября 2016 г. 19:53:22
Msg-id	CAB9893h=eDDHZ9m-0ENPQzqVdqjWBHhmiVCpfSZK7oUZVYHfXw@mail.gmail.com обсуждение исходный текст
Ответы	Re: query against single partition uses index, against master table does seq scan Re: query against single partition uses index, against master table does seq scan
Список	pgsql-performance

Дерево обсуждения

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.

В списке pgsql-performance по дате отправления:

Предыдущее

От: julyanto SUTANDANG
Дата: 19 сентября 2016 г., 13:05:52
Сообщение: Re: Postgresql 8.4 optimize for heavy select load

Следующее

От: Ganesh Kannan
Дата: 21 сентября 2016 г., 20:15:15
Сообщение: Re: query against single partition uses index, against master table does seq scan

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

query against single partition uses index, against master table does seq scan

Предыдущее

Следующее