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 <vi...@xs4all.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
