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

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 msExecution 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 msExecution 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.