Re: Query with no result set, really really slow adding ORBDER BY / LIMIT clause

[Marco Renzi <renzi.mrc@xxxxxxxxx>]

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 = 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 = tipofase_1.id)
                    ->  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
FROM            tipofase
JOIN       fase
ON         (fase.tipofase = (SELECT tipofase.id FROM tipofase WHERE tipofase.agendafrontoffice = true))

ORDER BY        fase.id 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.