Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause - Mailing list pgsql-performance

On 2017-04-20 13:16, Marco Renzi wrote:

Thanks Philip, yes i tried, but that is not solving, still slow. Take
a look at the log.

--------------------------------------------------------------------------------------------------------------------------
Limit  (cost=3.46..106.87 rows=10 width=4) (actual
time=396555.327..396555.327 rows=0 loops=1)
  ->  Nested Loop  (cost=3.46..214781.07 rows=20770 width=4) (actual
time=396555.326..396555.326 rows=0 loops=1)
        Join Filter: (tipofase.id [1] = fase.tipofase)
        ->  Index Scan Backward using test_prova_2 on fase
(cost=0.43..192654.24 rows=1474700 width=8) (actual
time=1.147..395710.190 rows=1475146 loops=1)
        ->  Materialize  (cost=3.03..6.34 rows=1 width=8) (actual
time=0.000..0.000 rows=0 loops=1475146)
              ->  Hash Semi Join  (cost=3.03..6.33 rows=1 width=8)
(actual time=0.081..0.081 rows=0 loops=1)
                    Hash Cond: (tipofase.id [1] = tipofase_1.id [2])
                    ->  Seq Scan on tipofase  (cost=0.00..3.02
rows=102 width=4) (actual time=0.003..0.003 rows=1 loops=1)
                    ->  Hash  (cost=3.02..3.02 rows=1 width=4) (actual
time=0.064..0.064 rows=0 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 0kB
                          ->  Seq Scan on tipofase tipofase_1
(cost=0.00..3.02 rows=1 width=4) (actual time=0.063..0.063 rows=0
loops=1)
                                Filter: agendafrontoffice
                                Rows Removed by Filter: 102
Planning time: 1.254 ms
Execution time: 396555.499 ms

--------------------------------------------------------------------------------------------------------------------------

THE ONLY WAY TO SPEEDUP I FOUND IS THIS ONE

SELECT  fase.id [3]
FROM            tipofase
JOIN       fase
ON         (fase.tipofase = (SELECT tipofase.id [1] FROM tipofase
WHERE tipofase.agendafrontoffice = true))

ORDER BY        fase.id [3] DESC   limit 10 offset 0

--------------------------------------------------------------------------------------------------------------------------

Limit  (cost=3.45..3.58 rows=10 width=4) (actual time=0.082..0.082
rows=0 loops=1)
  InitPlan 1 (returns $0)
    ->  Seq Scan on tipofase tipofase_1  (cost=0.00..3.02 rows=1
width=4) (actual time=0.072..0.072 rows=0 loops=1)
          Filter: agendafrontoffice
          Rows Removed by Filter: 102
  ->  Nested Loop  (cost=0.43..27080.93 rows=2118540 width=4) (actual
time=0.081..0.081 rows=0 loops=1)
        ->  Index Only Scan Backward using fase_test_prova_4 on fase
(cost=0.43..595.90 rows=20770 width=4) (actual time=0.080..0.080
rows=0 loops=1)
              Index Cond: (tipofase = $0)
              Heap Fetches: 0
        ->  Materialize  (cost=0.00..3.53 rows=102 width=0) (never
executed)
              ->  Seq Scan on tipofase  (cost=0.00..3.02 rows=102
width=0) (never executed)
Planning time: 0.471 ms
Execution time: 0.150 ms

--------------------------------------------------------------------------------------------------------------------------

Anyone knows?
I'm a bit worried about performance in my web app beacause sometimes
filters are written dinamically at the end, and i would like to avoid
these problems.

What was it that Philip suggested? I can't find his reply in the list and you didn't quote it...

Did you try reversing the order of the tables, so join fase to tipofase, instead of tipofase to fase.
Also, did you try a partial index on tipofase.id where tipofase.agendafrontoffice = true?