Fwd: [pgsql-performance] Daily digest v1.4804 (8 messages)

Hi Alex,

sorry, I missed your response somehow, got it only with today’s digest.

Thanks for the hint. I have basic idea how to investigate query perf issues.
I thought maybe I miss some understanding of the grounds.
It’ll take some time to get enough test data and I’ll try to take a look myself and come back after that.

Regards,
Val.

> From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net <mailto:akretschmer(at)spamfence(dot)net>>
> Subject: Re: why we do not create indexes on master
> Date: Dec 27 2016 19:04:27 GMT+3
> To: pgsql-performance(at)postgresql(dot)org <mailto:pgsql-performance(at)postgresql(dot)org>
>
>
> Valerii Valeev <valerii(dot)valeev(at)mail(dot)ru <mailto:valerii(dot)valeev(at)mail(dot)ru>> wrote:
>
>> Dear colleagues,
>>
>> can anyone please explain, why we do not create indexes on master?
>> In my case master / child design blindly follows partitioning guide https://
>> www.postgresql.org/docs/9.6/static/ddl-partitioning.html <http://www.postgresql.org/docs/9.6/static/ddl-partitioning.html>.
>> My collaborator was unhappy with performance of queries over master table with
>> filtering by one of fields
>>
>> SELECT * FROM “master" WHERE “field" BETWEEN x AND y
>>
>> (there are indexes for “field” on child tables).
>> He has created index on master once and found that the query returns 100x
>> faster.
>
> please show us explain analyse with/without index on master.
>
>
>
> Regards, Andreas Kretschmer
> --
> Andreas Kretschmer
> http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services