Optimize btree query

[bersace]

Requires CTE and thus Postgres 8.4. Here is a naïve comparison :

$ time psql btree_bloat-8.2-8.3.sql > old.out          
                                                                                                                                                                                                          
real    0m2,087s
user    0m0,035s
sys     0m0,011s
$ time psql btree_bloat.sql > new.out

real    0m0,107s
user    0m0,043s
sys     0m0,000s
$ diff -u old.out new.out 
$

cc @madtibo

[madtibo]

PostgreSQL version	9.5	9.6	10	11	12
btree/btree_bloat-superuser.sql	30,836 ms	31,127 ms	26,710 ms	25,374 ms	25,633 ms
btree/btree_bloat.sql	48,019 ms	48,775 ms	61,816 ms	59,598 ms	82,369 ms
btree/btree_bloat-8.2-8.3.sql	3845,079 ms	3828,218 ms	79,153 ms	74,919 ms	80,893 ms

[madtibo]

La contre performance en 12 vient du fait que le CTE est traité comme un sous-select. On retombe donc dans un sous select non optimal. Les perf sont meilleures avec un WITH avec le mot clé MATERIALIZED.

PostgreSQL version	12
btree/btree_bloat-superuser.sql	25,695 ms
btree/btree_bloat-materialized.sql	61,488 ms
btree/btree_bloat.sql	82,015 ms
btree/btree_bloat-8.2-8.3.sql	82,156 ms

Le problème, c'est que le mot clé MATERIALIZED n'arrive que en version 12... Est-ce qu'on crée une nouvelle version de la requête ?

[ioguix]

Hi,

I might be wrong, but it seems related to #9.

I don't understand why you compare with the 8.2-8.3 query as it seem your problem is on a recent release.

Moreover, maybe you should share the data you are testing on so I can reproduce.

Based on https://github.com/ioguix/pgsql-bloat-estimation/pull/16#issuecomment-673546794, the running time seem pretty decent to me.

I prefer avoiding the CTE as long as possible as it brings its own disadvantages.

[madtibo]

It is indeed the same problem as raised by julmon.

The query was split for 8.2 and 8.3 versions since CTE did not exist at the time.

The running time with the current query is really bad for versions 9.6 and before. The CTE is important to include for these versions.