public inbox for [email protected]  

help / color / mirror / Atom feed

From: kimaidou <[email protected]>
To: [email protected]
Subject: Re: Query plan for views and WHERE clauses, Luke is not using the index
Date: Mon, 21 Aug 2017 22:34:27 +0200
Message-ID: <CAMKXKO6JtgPxL46=2KnBZR_xozQ6OZ8t11YwQ6C=D5+1voQrUQ@mail.gmail.com> (raw)
In-Reply-To: <CAMKXKO7VXEEWfvsAYyWiAzca=fgAh6eSOU4V+BHUyz7uxJseWg@mail.gmail.com>
References: <CAMKXKO7VXEEWfvsAYyWiAzca=fgAh6eSOU4V+BHUyz7uxJseWg@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

Hi all

I also tried to change the values of join_collapse_limit and
rom_collapse_limit to higher values than default: 12, 50 or even 100, with
no improvement on the query plan.

Is this a typical behavior, or is there something particular in my query
that causes this big difference between the raw query and the view with
WHERE ?

Regards
Michaël

2017-08-18 18:46 GMT+02:00 kimaidou <[email protected]>:

> 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: <CAMKXKO6JtgPxL46=2KnBZR_xozQ6OZ8t11YwQ6C=D5+1voQrUQ@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