Partition pruning in pg11

[Mladen Gogala]

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:

https://www.postgresql.org/docs/current/ddl-partitioning.html




--
Mladen Gogala
Database Consultant
http://mgogala.byethost5.com

[Dimitri Fontaine]

Mladen Gogala <gogala...@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…

> 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:

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-EVQ%3D22hCCr8SfrFYO%3D8UL5qA%40mail.gmail.com

Regards,
--
Dimitri Fontaine

[Mladen Gogala]

On Sun, 17 Mar 2019 17:50:46 +0100, Dimitri Fontaine wrote:

> Mladen Gogala <gogala...@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…

I am a consultant. I can't test on the customer's machine(s). There is no
big secret here. And the whole thing is still in the planning phase, so
there is nothing to test.

>
>> 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:
>
> 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-
EVQ%3D22hCCr8SfrFYO%3D8UL5qA%40mail.gmail.com
>
> Regards,

The advantage of such a big index would be faster control of the
uniqueness condition and not having to search all the partitions. Without
a global unique index, every partition must be checked for existence of
the key. Under a reasonable assumption that each partition has one, it is
quite a few indexes to read. Both Oracle and DB2 have global indexes.
Personally, I am coming from the Oracle world and that is what I see in
the Oracle world. The customers that I am talking about usually want me
to migrate some of their databases from Oracle to Postgres, because of
the price. In some cases, I do that. And now, I've been asked about the
application which has Oracle partitioning.

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. The issue has been resolved to my satisfaction by the pg_hint_plan
extension, so now I am willing to discuss moving from Oracle to PgSQL
again. However, hints were not the only issue I've had with PgSQL.
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.
Proliferation of MVC frameworks like Django, Symfony or Hibernate makes
application porting from one database to another quite easy. Usually, the
hardest thing is getting the data from one database to another. PgSQL and
Oracle have very similar multi-versioning mechanisms and row level
locking, they are much more similar than Oracle and SQL Server. Ora2pg
helps a lot, too.

[Laurenz Albe]

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.

[Mladen Gogala]

On Wed, 20 Mar 2019 05:27:52 +0000, Laurenz Albe wrote:

> 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.

That is true. There are many features around partitioning in commercial
databases. I sort of liked PgSQL inheritance method and thought it ideal
for global indexes. We'll have to wait and see.

>
>> 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.

Well, hints were a deal breaker. That is a must have feature if you are
going to use database in production. Anyone who has ever been a DBA knows
that DBA is expected to solve the performance problem right then and
there. Waiting for an optimizer fix is simply not an option. Fortunately,
that situation has been resolved to my satisfaction, so it is a moot
point now. There is no point in rekindling the old flame wars.

> 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...

Well, that is the result of Oracle's putting too many options out in the
field. There are baselines, profiles, adaptive optimization, dynamic
sampling, cardinality feedback and statistics. So many options result in
a mayhem. I have my personal choices of the methods that I employ to
achieve acceptable result but this group is not about Oracle so I'll
spare you the details. Of course, there are different versions in the
field: 12cR1 is by far the most frequently used version. 12cR2 is slowly
getting into the field. 18c is mostly being played with, no real serious
use. Each of these versions behaves differently from the others. Funny
thing is that Oracle has adopted PgSQL "most popular values" histogram in
12cR1, after PgSQL has had it at least since the version 8.0, the first
PgSQL version that I have ever worked with.

>
>> 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.

Interesting way of thinking about partitioning. I'll have to give it some
thought.