Performance degrade in Planning Time to find appropriate Partial Index - Mailing list pgsql-performance
| From | Meenatchi Sandanam |
|---|---|
| Subject | Performance degrade in Planning Time to find appropriate Partial Index |
| Date | |
| Msg-id | CAJJu9dMg3662E-wR25ZyAJwVdG7Cz56r-8Bw4ejfT9i42Oo2LA@mail.gmail.com Whole thread Raw |
| Responses |
Re: Performance degrade in Planning Time to find appropriatePartial Index
(Laurenz Albe <laurenz.albe@cybertec.at>)
Re: Performance degrade in Planning Time to find appropriate Partial Index (Michael Loftis <mloftis@wgops.com>) |
| List | pgsql-performance |
I have created a table with 301 columns(ID, 150 BIGINT, 150 TEXT). The table contains multiple form data differentiated by ID range. Hence a column contains more than one form data. To achieve Unique Constraint and Indexing per form, I chose PostgreSQL Partial Indexes which suits my requirement. I have created Partial Indexes with ID Range as criteria and it provides Uniqueness and Indexing per form basis as expected. But DML operations on a particular form scans all the Indexes created for the entire table instead of scanning the Indexes created for that particular form ID Range. This degrades Planner Performance and Query Time more than 10 times as below,
Query Result for the table with 3000 Partial Indexes(15 Indexes per form) :
To ensure that the Planning Time 99.287 ms is not the time taken for scanning 15 Indexes for the form, I have created only 15 Indexes for the table and got the result as below,
Query Result for the table with 3000 Partial Indexes(15 Indexes per form) :
explain analyse select id from form_data_copy where id between 3001 and 4000 and bigint50=789;Query Result for the table with no Indexes(with same record count as above table) :
QUERY PLAN
------------------------------------------------------------ ------------------------------ ------------------------------ ------------------
Index Scan using form_data_1_bigint50_3000 on form_data_copy (cost=0.28..8.29 rows=1 width=8) (actual time=0.057..0.057 rows=0 loops=1)
Index Cond: (bigint50 = 789)
Planning time: 99.287 ms
Execution time: 0.112 ms
(4 rows)
Time: 103.967 ms
explain analyse select id from form_data_copy1 where id between 3001 and 4000 and bigint50=789; QUERY PLAN
------------------------------------------------------------ ------------------------------ ------------------------------ -------------------
Index Scan using form_data_copy1_fk1_idx on form_data_copy1 (cost=0.42..208.62 rows=1 width=8) (actual time=1.576..1.576 rows=0 loops=1)
Index Cond: ((id >= 3001) AND (id <= 4000))
Filter: (bigint50 = 789)
Rows Removed by Filter: 859
Planning time: 1.243 ms
Execution time: 1.701 ms
(6 rows)
Time: 5.891 ms
To ensure that the Planning Time 99.287 ms is not the time taken for scanning 15 Indexes for the form, I have created only 15 Indexes for the table and got the result as below,
explain analyse select id from form_data_copy1 where id between 3001 and 4000 and bigint50=789;It seems PGSQL scans all 3000 Indexes even though I provided the ID Range in the query. Please clarify whether my assumption is correct or the reason for this more Planning Time. Also, suggest me the way to reduce this planning time.
QUERY PLAN
------------------------------------------------------------ ------------------------------ ------------------------------ -----------------------
Index Scan using form_data_copy1_bigint50_3000 on form_data_copy1 (cost=0.28..8.29 rows=1 width=8) (actual time=0.025..0.025 rows=0 loops=1)
Index Cond: (bigint50 = 789)
Planning time: 3.017 ms
Execution time: 0.086 ms
(4 rows)
Time: 7.291 ms
pgsql-performance by date: