Unfortunately, I don't have enough data to test, but I am interested
whether version 11 has partition pruning? Partition pruning is an
optimizer procedure which determines which partitions are needed during
the parsing phase and restricts the optimization to the needed partitions only?
My second question, which will probably not be answered, is whether there
are any plans for global indexes? In other words, will it ever be
possible to impose a primary key on a partitioned table? Currently, that
is not possible:
Mladen Gogala <gogala.mladen@gmail.com> writes:
Unfortunately, I don't have enough data to test, but I am interested
whether version 11 has partition pruning? Partition pruning is an
optimizer procedure which determines which partitions are needed during
the parsing phase and restricts the optimization to the needed
partitions only?
Yes:
https://blog.2ndquadrant.com/partition-elimination-postgresql-11/
Though if you don't have enough data to test, one has to wonder why the answer is interesting for you…
EVQ%3D22hCCr8SfrFYO%3D8UL5qA%40mail.gmail.comMy second question, which will probably not be answered, is whether
there are any plans for global indexes? In other words, will it ever be
possible to impose a primary key on a partitioned table? Currently,
that is not possible:
Does not seem to be on the works. Also, what would be the advantage of
such a big index? After all PostgreSQL 11 already support primary keys spanning a partition tree.
https://www.postgresql.org/message-id/CAMkU%3D1xg5iJcuzjpj0a4Abbup-
Regards,
Both Oracle and DB2 have global indexes.
I used to be very hostile to the very idea of moving from Oracle to
PgSQL because of the hints and have dissuaded a few customers from
taking that route.
Partitioning was also a part of the problem. Fortunately,
the database in question is rather smallish, around 150 GB, so I am considering whether to use partitioning at all or not. The performance
might be good enough without partitioning.
On Wed, 20 Mar 2019 02:07:44 +0000, Mladen Gogala wrote:
Both Oracle and DB2 have global indexes.
There are certainly use cases for global indexes.
Partitioning is a fairly new feature in PostgreSQL, so it is not
surprising that it is not yet feature complete.
I talked to the people who wrote the code, and the hard thing about
global indexes is how to deal with dropped partitions.
You don't want to rebuild the index whenever you drop a partition.
I used to be very hostile to the very idea of moving from Oracle to
PgSQL because of the hints and have dissuaded a few customers from
taking that route.
I remember.
Only yesterday I listened to some Oracle DBAs talking about
profiles and baselines, and I got the impression that Oracle people are afraid of their optimizer and go to great lengths to keep it from doing
its work. I wonder why...
Partitioning was also a part of the problem. Fortunately,
the database in question is rather smallish, around 150 GB, so I am
considering whether to use partitioning at all or not. The performance
might be good enough without partitioning.
Yes, it is a wide-spread misconception that you have to partition bigger tables for performance reasons. True, it will speed up sequential scans which only have to scan some partitions. But the killer feature of partitioning is deleting old data.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 79:33:25 |
Calls: | 6,658 |
Calls today: | 4 |
Files: | 12,203 |
Messages: | 5,333,088 |
Posted today: | 1 |