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

Groups > comp.databases.postgresql > #858

Re: Partition pruning in pg11

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous 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