[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: Re: [PERFORM] why we do not create indexes on master
From: Valerii Valeev <valerii.valeev () mail ! ru>
Date: 2016-12-28 0:02:55
Message-ID: 3E61BD55-B989-4085-BBAB-4949664C0256 () mail ! ru
[Download RAW message or body]
David,
thanks a lot for the comments and for clarity. As I already responded to Andreas, I'm \
going to get some test data and try to investigate myself. Thought maybe I'm missing \
some common knowledge, that's why asked here before taking deeper look.
Regards,
Val.
> On Dec 27 2016, at 20:48, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Tue, Dec 27, 2016 at 10:38 AM, Valerii Valeev <valerii.valeev@mail.ru \
> <mailto:valerii.valeev@mail.ru>> wrote: Thank you David,
>
> I used same rationale to convince my colleague — it didn't work :)
> Sort of "pragmatic" person who does what seems working no matter what happens \
> tomorrow. So I'm seeking for better understanding of what's happening to have other \
> cause to convince him.
> Let me break it down once again. The experience is as follows:
>
> - partitioning follows the guide
>
> Only somewhat helpful...
>
> - master empty, no indexes
> - child tables have index on field "field"
> - query like
> SELECT * FROM "master" WHERE "field" BETWEEN ‘1' AND ‘2'
> takes more than 100 sec
>
> All retrieved data now exists in cache/buffers...
>
> - after that my mate adds index on "master"("field") — again, all data is in \
> child tables
> - same query takes under 1sec
>
> As Andreas said if you really want to explore what is happening here you need \
> to use EXPLAIN ANALYZE.
> Given the flow described above I/O retrieval performance differences, or the \
> attempt to query the table kicking off an ANALYZE, seems like possible contributing \
> factors.
>
> Questions I'd love to clarify:
>
> - Q1: is it correct that described situation happens because index created on \
> master does account data that is already there in child?
> No
>
> - Q2: is it correct that index on master created before inserting record to child \
> tables will not take into account this record?
> Yes
>
> - Q3: are there any other bad sides of indexes on master table?
>
> No
>
> David J.
>
[Attachment #3 (unknown)]
<html><head><meta http-equiv="Content-Type" content="text/html \
charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; \
-webkit-line-break: after-white-space;" class="">David,<div class=""><br \
class=""></div><div class="">thanks a lot for the comments and for clarity. As I \
already responded to Andreas, I'm going to get some test data and try to investigate \
myself.</div><div class="">Thought maybe I'm missing some common knowledge, that's \
why asked here before taking deeper look.</div><div class=""><br class=""></div><div \
class="">Regards,</div><div class="">Val.</div><div class=""> <br \
class=""><div><blockquote type="cite" class=""><div class="">On Dec 27 2016, at \
20:48, David G. Johnston <<a href="mailto:david.g.johnston@gmail.com" \
class="">david.g.johnston@gmail.com</a>> wrote:</div><br \
class="Apple-interchange-newline"><div class=""><div dir="ltr" class=""><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:arial,sans-serif" class="">On Tue, Dec 27, 2016 at 10:38 AM, \
Valerii Valeev </span><span dir="ltr" style="font-family:arial,sans-serif" \
class=""><<a href="mailto:valerii.valeev@mail.ru" target="_blank" \
class="">valerii.valeev@mail.ru</a>></span><span \
style="font-family:arial,sans-serif" class=""> wrote:</span><br class=""></div><div \
class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div \
style="word-wrap:break-word" class="">Thank you David,<div class=""><br \
class=""></div><div class="">I used same rationale to convince my colleague — it \
didn't work :)</div><div class="">Sort of "pragmatic" person who does what seems \
working no matter what happens tomorrow.</div><div class="">So I'm seeking for better \
understanding of what's happening to have other cause to convince him.</div><div \
class=""><br class=""></div><div class="">Let me break it down once again. The \
experience is as follows:</div><div class=""><br class=""></div><div class="">- \
partitioning follows the guide</div></div></blockquote><div class=""><br \
class=""></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">Only somewhat helpful...</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div style="word-wrap:break-word" class=""><div class="">- \
master empty, no indexes</div><div class="">- child tables have index on field \
"field"</div><div class="">- query like</div><div class=""><span \
class="m_1997549554280994350Apple-tab-span" \
style="white-space:pre-wrap"> </span>SELECT * FROM "master" WHERE "field" BETWEEN \
‘1' AND ‘2'</div><div class="">takes more than 100 \
sec</div></div></blockquote><div class=""><br class=""></div><div class=""><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">All retrieved \
data now exists in cache/buffers...</div></div><div class=""> </div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div style="word-wrap:break-word" class=""><div class="">- \
after that my mate adds index on "master"("field") — again, all data is in child \
tables</div><div class="">- same query takes under 1sec</div></div></blockquote><div \
class=""><br class=""></div><div class=""><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">As Andreas said if you really \
want to explore what is happening here you need to use EXPLAIN \
ANALYZE.</div></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br class=""></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">Given the flow \
described above I/O retrieval performance differences, or the attempt to query the \
table kicking off an ANALYZE, seems like possible contributing factors.</div><div \
class=""><br class=""></div><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word" \
class=""><div class=""><br class=""></div><div class="">Questions I'd love to \
clarify:</div><div class=""><br class=""></div><div class="">- Q1: is it correct that \
described situation happens because index created on master does account data that is \
already there in child? </div></div></blockquote><div class=""><br \
class=""></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">No</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div style="word-wrap:break-word" class=""><div class="">- \
Q2: is it correct that index on master created before inserting record to \
child tables will not take into account this \
record? </div></div></blockquote><div class=""><br class=""></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">Yes</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div style="word-wrap:break-word" class=""><div class="">- \
Q3: are there any other bad sides of indexes on master \
table?</div></div></blockquote><div class=""><br class=""></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">No</div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br \
class=""></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">David J.</div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br \
class=""></div></div></div></div> </div></blockquote></div><br \
class=""></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic