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

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?