Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djtRF-0002Qb-Mv for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 20:36:45 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djtRE-0001nM-TU for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 20:36:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1djtPT-00071A-8c for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 20:34:55 +0000 Received: from mail-vk0-x22d.google.com ([2607:f8b0:400c:c05::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djtPO-0000pV-56 for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 20:34:54 +0000 Received: by mail-vk0-x22d.google.com with SMTP id r199so53518177vke.4 for ; Mon, 21 Aug 2017 13:34:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=m1Z6HF1JE+QpR9V6jCG80eTRPDoESVBMxMX3A2XXAxc=; b=N0OLzWFipKgof6ysj3lJEqD+P1g/qLwkr9/okk+JcOo/+cGbCwDJrvRP9/iCSeV3xg r1cxobFKNPBRNEKHl8J6HE+tPdL2vaCnMLCEfHZRtN7upi0U+EfN4yDtAIhF1lLjhJSg pHgLrY+XCjGLCIJbMIED4rBaV0g345xWdJo3ng//4PGqHSVHMDeFE8gZj/q+aQ9P/Eod 5C77f3ZGDJXh555UW4hmfONQkWF8qITPhIzWW1eGkiYjgwYfAAyQbcKrtgnrO7VfZKQF oNh9++6ErNRNKaVlhl36lRAD4UifGPCYT0aulKU8cmlyQHCOzDFzIqA/y9n5nXmEhMB+ ZdGg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=m1Z6HF1JE+QpR9V6jCG80eTRPDoESVBMxMX3A2XXAxc=; b=tFZf/iaFl8UUCZP0+rwynqhC0aHkIHdoCzvw/ei5az96h2wbsANzPmAQfA32KTA61K z+xEu4Rix2kUh3qev15VP7FJj9R/UDFg9klflFwLG6RCxWFwUHTmhc4yC/JRaDcJ6o5w rAXnfswarGx0/lBW6sNYjYwxi1gFmpLBTU7Mln9mrbOqdclSQhF7gYRJOiEfIs/5jOcH 38fuamaIlozpPM0QMxAAlqUnYZf/xwnlZhYqErpd2ou2CcWTznhln89RnbcGm5PEOSUv B4EhSu4LNR4MIG/TBmt9irgM/2sSsiSlECW2bfOKXp4yWsd0gsdeLzmOOAEaKuJ+eM61 5fdA== X-Gm-Message-State: AHYfb5jbxTDZob/X5LONj8eC3FG4+3hcaJk5quzTX9+D6GtcPtJvxQkA xiR/DkLODfkYrCMp06xYE1mRS6oMIlq9 X-Received: by 10.31.172.193 with SMTP id v184mr11530740vke.147.1503347688172; Mon, 21 Aug 2017 13:34:48 -0700 (PDT) MIME-Version: 1.0 Received: by 10.31.137.85 with HTTP; Mon, 21 Aug 2017 13:34:27 -0700 (PDT) In-Reply-To: References: From: kimaidou Date: Mon, 21 Aug 2017 22:34:27 +0200 Message-ID: Subject: Re: Query plan for views and WHERE clauses, Luke is not using the index To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="001a1143eb56ed730905574968d0" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a1143eb56ed730905574968d0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=C3=ABl 2017-08-18 18:46 GMT+02:00 kimaidou : > 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 200= 00 > 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 > > --001a1143eb56ed730905574968d0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi all

I also tried to ch= ange the values of join_collapse_limit and rom_collapse_limit to higher va= lues than default: 12, 50 or even 100, with no improvement on the query pla= n.

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

Regards
Micha=C3=ABl

2017-08-18 18:46 GMT+= 02:00 kimaidou <kimaidou@gmail.com>:
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 JO= IN :
http= ://paste.debian.net/982003/

We have indexes on some f= ields ( foreign keys, and a GIST index for the PostGIS geometry field)
<= /div>If I use the raw SQL defining the view, and add a WHERE clause like:
WHERE "progression_geometry" && st_makeenvelope(172= 8327.03249295568093657,8240789.26074041239917278,1728608.109= 87572139129043,8240958.16933418624103069,3949)

the query = plan is "as expected", as it is using the spatial index (and othe= rs too). This query gets 100 lines from a "main" table containing= 20000 lines (and child tables having more). It is pretty fast and "lo= w 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&q= uot; && st_makeenvelope(1728327.03249295568093657,8240789.26074041239917278,1728608.10987572139129043,8240958.169334186241= 03069,3949)


The query plan is &q= uot;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=C3=ABl
=

--001a1143eb56ed730905574968d0--