[PERFORM] Query plan for views and WHERE clauses, Luke is not using the index

Поиск
Список
Период
Сортировка
От kimaidou
Тема [PERFORM] Query plan for views and WHERE clauses, Luke is not using the index
Дата
Msg-id CAMKXKO7VXEEWfvsAYyWiAzca=fgAh6eSOU4V+BHUyz7uxJseWg@mail.gmail.com
обсуждение исходный текст
Ответы Re: [PERFORM] Query plan for views and WHERE clauses, Luke is not using the index
Re: [PERFORM] Query plan for views and WHERE clauses, Luke is notusing the index
Список pgsql-performance
Дерево обсуждения
Hi all,

I have come across a unexpected behavior.
You can see full detail on an issue on the QGEP project in Github :
Basically, we have this view with some LEFT JOIN :
http://paste.debian.net/982003/

We have indexes on some fields ( foreign keys, and a GIST index for the PostGIS geometry field)
If I use the raw SQL defining the view, and add a WHERE clause like:

WHERE "progression_geometry" && st_makeenvelope(1728327.03249295568093657,8240789.26074041239917278,1728608.10987572139129043,8240958.16933418624103069,3949)

the query plan is "as expected", as it is using the spatial index (and others too). This query gets 100 lines from a "main" table containing 20000 lines (and child tables having more). It is pretty fast and "low cost"
See the query plan:

When we call the WHERE on the view:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT *
FROM "qgep"."vw_qgep_reach"
WHERE "progression_geometry" && st_makeenvelope(1728327.03249295568093657,8240789.26074041239917278,1728608.10987572139129043,8240958.16933418624103069,3949)


The query plan is "wrong", as PostgreSQL seems to consider it should do a seq scan on the tables, and only afterwards filter with the WHERE:
The query takes about 1 second instead of less than 100ms.

Do you have any hint on this kind of issue ?

Thanks in advance

Regards,
Michaël

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