pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance degrade in Planning Time to find appropriate Partial Index
7+ messages / 6 participants
[nested] [flat]
* Performance degrade in Planning Time to find appropriate Partial Index
@ 2018-03-01 11:09 Meenatchi Sandanam <meen.opm@gmail.com>
2018-03-01 13:03 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-01 14:16 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Michael Loftis <mloftis@wgops.com>
0 siblings, 2 replies; 7+ messages in thread
From: Meenatchi Sandanam @ 2018-03-01 11:09 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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) :
explain analyse select id from form_data_copy where id between 3001 and
4000 and bigint50=789;
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*
Query Result for the table with no Indexes(with same record count as above
table) :
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;
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
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance degrade in Planning Time to find appropriate Partial Index
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
@ 2018-03-01 13:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-02 13:49 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Nandakumar M <m.nanda92@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2018-03-01 13:03 UTC (permalink / raw)
To: Meenatchi Sandanam <meen.opm@gmail.com>; pgsql-performance@lists.postgresql.org
Meenatchi Sandanam wrote:
> 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) :
It is crazy to create 3000 partial indexes on one table.
No wonder planning and DML statements take very long, they have to consider all the
indexes.
> explain analyse select id from form_data_copy where id between 3001 and 4000 and bigint50=789;
Use a single index on (bigint50, id) for best performance.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance degrade in Planning Time to find appropriate Partial Index
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
2018-03-01 13:03 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-03-02 13:49 ` Nandakumar M <m.nanda92@gmail.com>
2018-03-02 14:29 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Nandakumar M @ 2018-03-02 13:49 UTC (permalink / raw)
To: Meenatchi Sandanam <meen.opm@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
Hi,
https://heapanalytics.com/blog/engineering/running-10-million-postgresql-indexes-in-production
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance degrade in Planning Time to find appropriate Partial Index
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
2018-03-01 13:03 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-02 13:49 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Nandakumar M <m.nanda92@gmail.com>
@ 2018-03-02 14:29 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-03-02 14:32 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2018-03-02 14:29 UTC (permalink / raw)
To: Nandakumar M <m.nanda92@gmail.com>; +Cc: Meenatchi Sandanam <meen.opm@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-02 14:49 GMT+01:00 Nandakumar M <m.nanda92@gmail.com>:
> Hi,
>
> https://heapanalytics.com/blog/engineering/running-10-
> million-postgresql-indexes-in-production
>
> From the link shared above, it looks like what Meenatchi has done should
> work.
>
It can be different situation, there are not specified indexes per table.
And if some projects works, it doesn't mean, so they are well designed.
PostgreSQL has not column storage. Look on column databases. They are
designed for extra wide tables.
Regards
Pavel
>
> Do the conditions on the partial index and query match exactly? (
> greater than / greater than equals mismatch maybe?)
>
> If conditions for those partial indexes are mutually exclusive and the
> query has a matching condition then Postgres can use that index alone.
> Are we missing something here?
>
> Regards,
> Nanda
>
> On Thu, Mar 1, 2018 at 6:33 PM, Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> > Meenatchi Sandanam wrote:
> >> 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) :
> >
> > It is crazy to create 3000 partial indexes on one table.
> >
> > No wonder planning and DML statements take very long, they have to
> consider all the
> > indexes.
> >
> >> explain analyse select id from form_data_copy where id between 3001 and
> 4000 and bigint50=789;
> >
> > Use a single index on (bigint50, id) for best performance.
> >
> > Yours,
> > Laurenz Albe
> > --
> > Cybertec | https://www.cybertec-postgresql.com
> >
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance degrade in Planning Time to find appropriate Partial Index
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
2018-03-01 13:03 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-02 13:49 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Nandakumar M <m.nanda92@gmail.com>
2018-03-02 14:29 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Pavel Stehule <pavel.stehule@gmail.com>
@ 2018-03-02 14:32 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2018-03-02 14:32 UTC (permalink / raw)
To: Nandakumar M <m.nanda92@gmail.com>; +Cc: Meenatchi Sandanam <meen.opm@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-02 15:29 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
>
>
> 2018-03-02 14:49 GMT+01:00 Nandakumar M <m.nanda92@gmail.com>:
>
>> Hi,
>>
>> https://heapanalytics.com/blog/engineering/running-10-millio
>> n-postgresql-indexes-in-production
>>
>> From the link shared above, it looks like what Meenatchi has done should
>> work.
>>
>
> It can be different situation, there are not specified indexes per table.
> And if some projects works, it doesn't mean, so they are well designed.
>
> PostgreSQL has not column storage. Look on column databases. They are
> designed for extra wide tables.
>
read the article:
1. Probably they use Citus
2. Since partial indexes are so easy to create and work with, we’ve wound
up with over 10 million partial indexes across our entire cluster.
> Regards
>
> Pavel
>
>
>>
>> Do the conditions on the partial index and query match exactly? (
>> greater than / greater than equals mismatch maybe?)
>>
>> If conditions for those partial indexes are mutually exclusive and the
>> query has a matching condition then Postgres can use that index alone.
>> Are we missing something here?
>>
>> Regards,
>> Nanda
>>
>> On Thu, Mar 1, 2018 at 6:33 PM, Laurenz Albe <laurenz.albe@cybertec.at>
>> wrote:
>> > Meenatchi Sandanam wrote:
>> >> 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) :
>> >
>> > It is crazy to create 3000 partial indexes on one table.
>> >
>> > No wonder planning and DML statements take very long, they have to
>> consider all the
>> > indexes.
>> >
>> >> explain analyse select id from form_data_copy where id between 3001
>> and 4000 and bigint50=789;
>> >
>> > Use a single index on (bigint50, id) for best performance.
>> >
>> > Yours,
>> > Laurenz Albe
>> > --
>> > Cybertec | https://www.cybertec-postgresql.com
>> >
>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance degrade in Planning Time to find appropriate Partial Index
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
@ 2018-03-01 14:16 ` Michael Loftis <mloftis@wgops.com>
2018-03-01 14:39 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Moreno Andreo <moreno.andreo@evolu-s.it>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Loftis @ 2018-03-01 14:16 UTC (permalink / raw)
To: Meenatchi Sandanam <meen.opm@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Mar 1, 2018 at 03:10 Meenatchi Sandanam <meen.opm@gmail.com> wrote:
> 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)
> :
>
This smells like you’ve failed to normalize your data correctly. 3k indexes
to ensure uniqueness ? It sounds a lot more like you need 15 tables for 15
forms ... perhaps with a view for reading or maybe 1/15th of the columns to
begin with by having a form_type column...or perhaps like an index function
for the unique constraint....such that the output of the function is the
normalized portion of data that’s required to be unique....
If you’ve really got 3k different uniqueness criteria differing by “id”
ranges then it sounds like an expression index with a function spitting out
the hash of uniqueness but that’d still be hairy, at least you wouldn’t eat
the time on every read though. But I’d reduce that id range based problem
to include a unique_type indicator column instead.
> --
"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance degrade in Planning Time to find appropriate Partial Index
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
2018-03-01 14:16 ` Re: Performance degrade in Planning Time to find appropriate Partial Index Michael Loftis <mloftis@wgops.com>
@ 2018-03-01 14:39 ` Moreno Andreo <moreno.andreo@evolu-s.it>
0 siblings, 0 replies; 7+ messages in thread
From: Moreno Andreo @ 2018-03-01 14:39 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Il 01/03/2018 15:16, Michael Loftis ha
scritto:<br>
</div>
<blockquote type="cite"
cite="mid:CAHDg04tGO1Hh35DMG04aoZCi_9Pw3dDe1C9C0v2o8qVMvG+aWg@mail.gmail.com">
<div><br>
<div class="gmail_quote">
<div dir="auto">On Thu, Mar 1, 2018 at 03:10 Meenatchi
Sandanam <<a href="mailto:meen.opm@gmail.com"
moz-do-not-send="true">meen.opm@gmail.com</a>> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><span
style="color:rgb(0,0,0);font-family:latoregular,helvetica,arial;font-size:16.2px;letter-spacing:0.1px">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, </span><br
style="color:rgb(0,0,0);font-family:latoregular,helvetica,arial;font-size:16.2px;letter-spacing:0.1px">
<br
style="color:rgb(0,0,0);font-family:latoregular,helvetica,arial;font-size:16.2px;letter-spacing:0.1px">
<span
style="color:rgb(0,0,0);font-family:latoregular,helvetica,arial;font-size:16.2px;letter-spacing:0.1px">Query
Result for the table with 3000 Partial Indexes(15
Indexes per form) : </span></div>
</blockquote>
<div dir="auto"><br>
</div>
<div dir="auto">This smells like you’ve failed to normalize
your data correctly. 3k indexes to ensure uniqueness ? It
sounds a lot more like you need 15 tables for 15 forms ... <br>
</div>
</div>
</div>
</blockquote>
... or a column that specifies, e.g., the form ID. If all form has
not the same number of BIGINT and TEXT, keep the maximum value and
fill only the requested ones.<br>
<br>
You can also use the EAV schema, where the Entity is the form, the
Attribute is the field, and the Value... is the value.<br>
<br>
CREATE TABLE tbl(<br>
id bigint,<br>
entity integer,<br>
attribute integer, --(or string, as you need)<br>
value_int bigint,<br>
value_string text<br>
);<br>
<br>
This way you'll get more rows, but very thin, and with not more than
3 or 4 indexes (based on the querues you need to perform) you can
retrieve values quickly.<br>
<br>
My 2 cent<br>
Moreno.-<br>
</body>
</html>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-03-02 14:32 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-01 11:09 Performance degrade in Planning Time to find appropriate Partial Index Meenatchi Sandanam <meen.opm@gmail.com>
2018-03-01 13:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-03-02 13:49 ` Nandakumar M <m.nanda92@gmail.com>
2018-03-02 14:29 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-03-02 14:32 ` Pavel Stehule <pavel.stehule@gmail.com>
2018-03-01 14:16 ` Michael Loftis <mloftis@wgops.com>
2018-03-01 14:39 ` Moreno Andreo <moreno.andreo@evolu-s.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox