MIME-Version: 1.0
From: kimaidou <kimaidou@gmail.com>
Date: Fri, 18 Aug 2017 18:46:47 +0200
Message-ID: 
 <CAMKXKO7VXEEWfvsAYyWiAzca=fgAh6eSOU4V+BHUyz7uxJseWg@mail.gmail.com>
Subject: Query plan for views and WHERE clauses, Luke is not using the index
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="001a1145343c2ffb5d055709e102"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1145343c2ffb5d055709e102
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=C3=ABl

--001a1145343c2ffb5d055709e102
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div><div><div><div><div>Hi all,<br><br></div>I =
have come across a unexpected behavior.<br></div>You can see full detail on=
 an issue on the QGEP project in Github : <br></div><div><a href=3D"https:/=
/github.com/QGEP/QGEP/issues/308#issuecomment-323122514">https://github.com=
/QGEP/QGEP/issues/308#issuecomment-323122514</a><br><br></div>Basically, we=
 have this view with some LEFT JOIN : <br><a href=3D"http://paste.debian.ne=
t/982003/">http://paste.debian.net/982003/</a><br><br></div>We have indexes=
 on some fields ( foreign keys, and a GIST index for the PostGIS geometry f=
ield)<br></div>If I use the raw SQL defining the view, and add a WHERE clau=
se like:<br><br>WHERE &quot;progression_geometry&quot; &amp;&amp; st_makeen=
velope(1728327.03249295568093657,8240789.26074041239917278,1728608.10987572=
139129043,8240958.16933418624103069,3949)<br><br></div>the query plan is &q=
uot;as expected&quot;, as it is using the spatial index (and others too). T=
his query gets 100 lines from a &quot;main&quot; table containing 20000 lin=
es (and child tables having more). It is pretty fast and &quot;low cost&quo=
t;<br></div><div>See the query plan:</div><div><a href=3D"https://explain.d=
epesz.com/s/6Qgb">https://explain.depesz.com/s/6Qgb</a><br></div><div><br><=
/div>When we call the WHERE on the view:<br><br>EXPLAIN (ANALYZE, COSTS, VE=
RBOSE, BUFFERS)<br>SELECT *<br>FROM &quot;qgep&quot;.&quot;vw_qgep_reach&qu=
ot; <br>WHERE &quot;progression_geometry&quot; &amp;&amp; st_makeenvelope(1=
728327.03249295568093657,8240789.26074041239917278,1728608.1098757213912904=
3,8240958.16933418624103069,3949)<br><div><div><br></div><div><br></div><di=
v>The query plan is &quot;wrong&quot;, as PostgreSQL seems to consider it s=
hould do a seq scan on the tables, and only afterwards filter with the WHER=
E:</div><div><a href=3D"https://explain.depesz.com/s/wXV">https://explain.d=
epesz.com/s/wXV</a><br></div><div><pre><span class=3D"gmail-pl-k">The query=
 takes about 1 second instead of less than 100ms.<br><br></span></pre><pre>=
<span class=3D"gmail-pl-k">Do you have any hint on this kind of issue ?<br>=
<br></span></pre><pre><span class=3D"gmail-pl-k">Thanks in advance<br><br><=
/span></pre><pre><span class=3D"gmail-pl-k">Regards,<br></span></pre><pre><=
span class=3D"gmail-pl-k">Micha=C3=ABl<br></span></pre></div></div></div>

--001a1145343c2ffb5d055709e102--