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

Groups > comp.databases.postgresql > #856

Re: Partition pruning in pg11

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web