0

I have been working on SQL Server for more than 10 years, now I have a database which I want to confirm if it is viable to change it to PostgreSQL, this (simplified) query runs in about 100ms in SQL Server but takes 4 times longer on PostgreSQL, I am not sure if I am missing any index or command to execute it more efficiently in PostgreSQL, PostgreSQL ignored any index I tryed (like the BRIN index), the execution plan is similar to SQL Server with HASH JOINs.

Is there anything in PostgreSQL to improve the performance of this query?

explain analyze
select * from g where id_g in (
select distinct gID
  from (select distinct eID, TipogID
          from (select cID, vID, pID, 35 - SUM(DISTINCT CASE WHEN Filtros.gID IS NOT NULL THEN TipogID ELSE 0 END) AS TipogID
                  from (select distinct cID, vID, pID
                          from f)fat
           cross join LATERAL (VALUES(cID), (vID), (pID))Tipos(eID)
             join g_e_arvore
                    on g_e_arvore.eID = Tipos.eID
             join (values(4123),(21888))Filtros(gID)
                    on g_e_arvore.gID = Filtros.gID
              GROUP BY cID, vID, pID
              HAVING COUNT(DISTINCT CASE WHEN Filtros.gID IS NOT NULL THEN TipogID END) >= 0)pvt
       cross join LATERAL (VALUES(cID), (vID), (pID))Tipos(eID) )pvt
  join g_e_arvore
    on g_e_arvore.eID = pvt.eID
 where (pvt.TipogID = 0 or pvt.TipogID = g_e_arvore.TipogID)
   )
Hash Join  (cost=26226.00..26426.19 rows=40 width=37) (actual time=416.577..417.881 rows=249 loops=1)
  Hash Cond: (g.id_g = g_e_arvore.gid)
  ->  Seq Scan on g  (cost=0.00..175.19 rows=9519 width=37) (actual time=0.028..0.511 rows=9519 loops=1)
  ->  Hash  (cost=26225.50..26225.50 rows=40 width=4) (actual time=416.539..416.539 rows=249 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 17kB
        ->  Unique  (cost=26224.90..26225.10 rows=40 width=4) (actual time=416.387..416.508 rows=249 loops=1)
              ->  Sort  (cost=26224.90..26225.00 rows=40 width=4) (actual time=416.386..416.419 rows=1208 loops=1)
                    Sort Key: g_e_arvore.gid
                    Sort Method: quicksort  Memory: 105kB
                    ->  Nested Loop  (cost=24941.82..26223.84 rows=40 width=4) (actual time=397.494..416.031 rows=1208 loops=1)
                          ->  Unique  (cost=24941.41..24948.34 rows=600 width=12) (actual time=397.144..407.458 rows=2459 loops=1)
                                ->  Sort  (cost=24941.41..24943.72 rows=924 width=12) (actual time=397.142..403.422 rows=60783 loops=1)
                                      Sort Key: "*VALUES*".column1, ((35 - sum(DISTINCT CASE WHEN ("*VALUES*_2".column1 IS NOT NULL) THEN g_e_arvore_1.tipogid ELSE 0 END)))
                                      Sort Method: external merge  Disk: 1560kB
                                      ->  Nested Loop  (cost=24845.87..24895.89 rows=924 width=12) (actual time=295.653..364.935 rows=60783 loops=1)
                                            ->  GroupAggregate  (cost=24845.87..24872.02 rows=308 width=20) (actual time=295.647..339.713 rows=20261 loops=1)
                                                  Group Key: f.cid, f.vid, f.pid
                                                  Filter: (count(DISTINCT CASE WHEN ("*VALUES*_2".column1 IS NOT NULL) THEN g_e_arvore_1.tipogid ELSE NULL::integer END) >= 0)
                                                  ->  Sort  (cost=24845.87..24848.18 rows=923 width=20) (actual time=295.624..296.803 rows=29694 loops=1)
                                                        Sort Key: f.cid, f.vid, f.pid
                                                        Sort Method: quicksort  Memory: 3088kB
                                                        ->  Hash Join  (cost=20624.99..24800.41 rows=923 width=20) (actual time=195.150..277.736 rows=29694 loops=1)
                                                              Hash Cond: ("*VALUES*_1".column1 = g_e_arvore_1.eid)
                                                              ->  Nested Loop  (cost=19437.06..23128.59 rows=126567 width=16) (actual time=185.929..252.949 rows=139239 loops=1)
                                                                    ->  HashAggregate  (cost=19437.06..19858.95 rows=42189 width=12) (actual time=185.921..197.357 rows=46413 loops=1)
                                                                          Group Key: f.cid, f.vid, f.pid
                                                                          ->  Seq Scan on f  (cost=0.00..16272.89 rows=421889 width=12) (actual time=0.023..28.384 rows=421889 loops=1)
                                                                    ->  Values Scan on "*VALUES*_1"  (cost=0.00..0.04 rows=3 width=4) (actual time=0.000..0.001 rows=3 loops=46413)
                                                              ->  Hash  (cost=1187.13..1187.13 rows=64 width=12) (actual time=9.206..9.207 rows=2026 loops=1)
                                                                    Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 104kB
                                                                    ->  Hash Join  (cost=0.05..1187.13 rows=64 width=12) (actual time=0.034..8.813 rows=2026 loops=1)
                                                                          Hash Cond: (g_e_arvore_1.gid = "*VALUES*_2".column1)
                                                                          ->  Seq Scan on g_e_arvore g_e_arvore_1  (cost=0.00..965.41 rows=58941 width=12) (actual time=0.009..3.171 rows=58941 loops=1)
                                                                          ->  Hash  (cost=0.03..0.03 rows=2 width=4) (actual time=0.009..0.009 rows=2 loops=1)
                                                                                Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                                                                ->  Values Scan on "*VALUES*_2"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.002..0.002 rows=2 loops=1)
                                            ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=4) (actual time=0.000..0.001 rows=3 loops=20261)
                          ->  Index Only Scan using g_e_arvore_pkey on g_e_arvore  (cost=0.41..2.11 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=2459)
                                Index Cond: (eid = "*VALUES*".column1)
                                Filter: ((((35 - sum(DISTINCT CASE WHEN ("*VALUES*_2".column1 IS NOT NULL) THEN g_e_arvore_1.tipogid ELSE 0 END))) = 0) OR (((35 - sum(DISTINCT CASE WHEN ("*VALUES*_2".column1 IS NOT NULL) THEN g_e_arvore_1.tipogid ELSE 0 END))) = tipogid))
                                Rows Removed by Filter: 6
                                Heap Fetches: 0
Planning Time: 1.451 ms
Execution Time: 423.008 ms
  • Consider instructions in tag info of [postgresql-performance] – Erwin Brandstetter Apr 17 at 21:11
  • There are two obvious low hanging fruites (but unfortunately they won't change much): the outer most distinct in the sub-query is useless and you can increase your work_mem so that the intermediate sort step won't be done on disk but in memory. I also think that this from (select distinct cID, vID, pID from f) fat is useless as well, as you are doing a group by on those three columns just in the next level of the query. I think that could be replaced with select cID, vID, pID, 35 - SUM(DISTINCT ....) from f cross join .... group by ... – a_horse_with_no_name Apr 18 at 6:21
  • I also wonder if it would be possible to do the aggregation and joining first, and then unpivot the result of that, instead of doing two unpivot steps in the sub-query – a_horse_with_no_name Apr 18 at 6:24
  • @a_horse_with_no_name the query becomes significantly slower when removing the two inner DISTINCT, there were no difference when removing the outer, setting work_mem to 256MB made no difference, setting to 1MB made it slower, for the second unpivot, I need to calculate TipogID and PostgreSQL (As other RDBMS) does not support DISTINCT in window function yet (version 11). – EduardoS Apr 18 at 15:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.