public inbox for [email protected]
help / color / mirror / Atom feedFrom: kimaidou <[email protected]>
To: [email protected]
Subject: Query plan for views and WHERE clauses, Luke is not using the index
Date: Fri, 18 Aug 2017 18:46:47 +0200
Message-ID: <CAMKXKO7VXEEWfvsAYyWiAzca=fgAh6eSOU4V+BHUyz7uxJseWg@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
Hi all,
I have come across a unexpected behavior.
You can see full detail on an issue on the QGEP project in Github :
https://github.com/QGEP/QGEP/issues/308#issuecomment-323122514
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:
https://explain.depesz.com/s/6Qgb
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:
https://explain.depesz.com/s/wXV
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Query plan for views and WHERE clauses, Luke is not using the index
In-Reply-To: <CAMKXKO7VXEEWfvsAYyWiAzca=fgAh6eSOU4V+BHUyz7uxJseWg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox