Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #852 > unrolled thread

Partition pruning in pg11

Back to article view | Back to comp.databases.postgresql

Contents

  Partition pruning in pg11 Mladen Gogala <gogala.mladen@gmail.com> - 2019-03-17 03:15 +0000
    Re: Partition pruning in pg11 Dimitri Fontaine <dim@tapoueh.org> - 2019-03-17 17:50 +0100
      Re: Partition pruning in pg11 Mladen Gogala <gogala.mladen@gmail.com> - 2019-03-20 02:07 +0000
        Re: Partition pruning in pg11 Laurenz Albe <laurenz@nospam.pn> - 2019-03-20 05:27 +0000
          Re: Partition pruning in pg11 Mladen Gogala <gogala.mladen@gmail.com> - 2019-03-20 10:19 +0000

#852 — Partition pruning in pg11

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

[toc] | [next] | [standalone]

#853

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…

> 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

[toc] | [prev] | [next] | [standalone]

#856

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

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

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.


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

[toc] | [prev] | [next] | [standalone]

#857

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.

[toc] | [prev] | [next] | [standalone]

#858

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.




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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web