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

Sorry Vinny, this was what Philip suggested:

Have you tried changing your query to:

SELECT id <http://fase.id>
FROM fase
WHERE tipofase IN (SELECT ID from tipofase WHERE agendafrontoffice = true)
ORDER BY id <http://fase.id> DESC
LIMIT 10 OFFSET 0

And this is my 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

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

2017-04-20 13:54 GMT+02:00 vinny <vinny(at)xs4all(dot)nl>:

> 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?
>

--
-------------------------------------------------------------------------------------------------------------------------------------------
Ing. Marco Renzi
OCA - Oracle Certified Associate Java SE7 Programmer
OCP - Oracle Certified Mysql 5 Developer

via Zegalara 57
62014 Corridonia(MC)
Mob: 3208377271

"The fastest way to change yourself is to hang out with people who are
already the way you want to be" Reid Hoffman