[PERFORM] Query with no result set, really really slow adding ORBDER BY / LIMIT clause

Поиск
Список
Период
Сортировка
От Marco Renzi
Тема [PERFORM] Query with no result set, really really slow adding ORBDER BY / LIMIT clause
Дата
Msg-id CAHmtSMa_UnK_8q5-Hc2BCWk9TEWXhexmcn=ZxXkrfsWuJvje6Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause
Список pgsql-performance
Hi!, i've currently a big problem using  ORBDER BY / LIMIT in a query with no result set.
If i add the order by/limit clause it runs really really slow.


QUERY 1 FAST:
--------------------------------

SELECT fase.id
FROM tipofase
JOIN fase
ON (fase.tipofase = tipofase.id)
WHERE tipofase.agendafrontoffice = true

EXPLAIN ANALYZE:

Nested Loop (cost=0.43..790.19 rows=14462 width=4) (actual time=0.079..0.079 rows=0 loops=1)
  ->  Seq Scan on tipofase  (cost=0.00..3.02 rows=1 width=4) (actual time=0.077..0.077 rows=0 loops=1)
Filter: agendafrontoffice
Rows Removed by Filter: 102
-> Index Only Scan using fase_test_prova_4 on fase (cost=0.43..595.59 rows=19158 width=8) (never executed)
Index Cond: (tipofase = tipofase.id)
Heap Fetches: 0
Planning time: 0.669 ms
Execution time: 0.141 ms

---

It's perfect because it starts from tipofase, where there are no agendafrontoffice = true

fase_test_prova_4 is a btree index ON (fase.tipofase, fase.id)
fase.id is PRIMARY key on fase,

tipofase.id is PRIMARY key on tipofase,
fase.tipofase is FK on tipofase.id
and tipofase.agendafrontoffice is a boolean.
I've also created a btree index on tipofase.agendafrontoffice.

**fase** is a large table with 1.475.146 records. There are no rows in the table matching tipofase.agendafrontoffice = true, so the result set is empty(QUERY 1) 



QUERY 2 SLOW(WITH limit and order by):
--------------------------------


SELECT fase.id
FROM tipofase
JOIN fase
ON (fase.tipofase = tipofase.id)
WHERE tipofase.agendafrontoffice = true
ORDER BY fase.id DESC limit 10 offset 0

Limit (cost=0.43..149.66 rows=10 width=4) (actual time=173853.131..173853.131 rows=0 loops=1)
-> Nested Loop (cost=0.43..215814.25 rows=14462 width=4) (actual time=173853.130..173853.130 rows=0 loops=1)
Join Filter: (fase.tipofase = tipofase.id)
-> Index Scan Backward using test_prova_2 on fase (cost=0.43..193684.04 rows=1475146 width=8) (actual time=1.336..173128.418 rows=1475146 loops=1)
-> Materialize (cost=0.00..3.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1475146)
-> Seq Scan on tipofase (cost=0.00..3.02 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=1)
Filter: agendafrontoffice
Rows Removed by Filter: 102
Planning time: 0.685 ms
Execution time: 173853.221 ms


Really really slow..... looks like the planner is not doing a good job.
PostgreSQL 9.4.1, compiled by Visual C++ build 1800, 64-bit


I also run VACUUM AND VACUUM ANALYZE on both table
I tried to play with the
"alter table tipofase alter column agendafrontoffice set statistics 2"
but nothing.

Thanks in advance Marco


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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Armand Pirvu (home)"
Дата:
Сообщение: Re: [PERFORM] update from performance question
Следующее
От: Marco Renzi
Дата:
Сообщение: Re: [PERFORM] Query with no result set, really really slow addingORBDER BY / LIMIT clause