From: Moreno Andreo <moreno.andreo@evolu-s.it>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Performance degrade in Planning Time to find appropriate Partial Index
Date: Thu, 1 Mar 2018 15:39:46 +0100
Message-ID: <d4ddb4f2-0bfb-2678-85a2-d6ba62d19d09@evolu-s.it> (raw)
In-Reply-To: <CAHDg04tGO1Hh35DMG04aoZCi_9Pw3dDe1C9C0v2o8qVMvG+aWg@mail.gmail.com>
References: <CAJJu9dMg3662E-wR25ZyAJwVdG7Cz56r-8Bw4ejfT9i42Oo2LA@mail.gmail.com>
<CAHDg04tGO1Hh35DMG04aoZCi_9Pw3dDe1C9C0v2o8qVMvG+aWg@mail.gmail.com>
<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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: moreno.andreo@evolu-s.it, pgsql-performance@lists.postgresql.org
Subject: Re: Performance degrade in Planning Time to find appropriate Partial Index
In-Reply-To: <d4ddb4f2-0bfb-2678-85a2-d6ba62d19d09@evolu-s.it>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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