| From: | Valerii Valeev <valerii(dot)valeev(at)mail(dot)ru> |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | why we do not create indexes on master |
| Date: | 2016-12-27 15:22:13 |
| Message-ID: | 01F901FD-F29D-4A46-ACB4-4E6FD75EE7E6@mail.ru |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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 <https://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.
I have naive idea that it won’t help if index is created before the data is there — i.e. indexes on master aren’t updated when data loaded to child table.
I’m curious is it right or it’s something less primitive.
Thanks and have a happy holidays!
Val.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andreas Kretschmer | 2016-12-27 16:04:27 | Re: why we do not create indexes on master |
| Previous Message | Michael Paquier | 2016-12-27 11:01:04 | Re: Invalid page header in block 25561983 of relation pg_tblspc |