Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eKxdn-0007uf-Bd for pgsql-performance@arkaria.postgresql.org; Sat, 02 Dec 2017 02:34:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eKxdm-0000Vg-RY for pgsql-performance@arkaria.postgresql.org; Sat, 02 Dec 2017 02:34:54 +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 1eKxdm-0000VW-Gy for pgsql-performance@lists.postgresql.org; Sat, 02 Dec 2017 02:34:54 +0000 Received: from mail-wm0-x242.google.com ([2a00:1450:400c:c09::242]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eKxde-0005k4-Uc for pgsql-performance@lists.postgresql.org; Sat, 02 Dec 2017 02:34:53 +0000 Received: by mail-wm0-x242.google.com with SMTP id b76so6238639wmg.1 for ; Fri, 01 Dec 2017 18:34:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:subject:from:in-reply-to:date:cc:message-id:references :to; bh=VIqyoLEMtV/scROiHRWzuc/v7OQrg53Vj0Wi5B2nNis=; b=SS3Pvfdt2p8u1wdXKK9snXP9XyGBlNUP8RKD+tLJs+Gm0qTLZwX61SonFa49VUs6Uf 98H2jWb2i3yGwl7oqgQ1ee0DPdte2qEs7xhUVbOmk8SdYsK2jj0yhxdOcLxi4PKkeUw1 wpzPFLwcKKA6gM0NIxQjzFWgdJAUeaMxG4QTsciUSxDcApaeWPzLDZUD2u4Y6F8YFQsC mJURQoe9nhQ/WhpxY1+fhHI6jjsA5PD/qslEQ4AQOLEs/TdVtFQrmlZARhGu9GgmJH0x NGO8vWfYS+oiSjmhcfxvbAMRno6sIsetyAKMlBJcbf1U33ifskTKxg4mnsVLxVxv72rl gJqw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:subject:from:in-reply-to:date:cc :message-id:references:to; bh=VIqyoLEMtV/scROiHRWzuc/v7OQrg53Vj0Wi5B2nNis=; b=gxtHOPRLPlEgMYtZf7FfDS9HZ+mcXDlEc/I28Hm0vhgR84bxxHqg1RHsvTvKRE47eg HES1r01H9fs/S69yChe/KW40u8JtbmlimCm/h+zEO8/GxXKLWJBMq4UTOzXJfi50l5au u+jpJKpgzXh5zM/ycpSO15U/eB9J3963z/jqJfNo6vbxiM03l38GS2gCqYY75jI3M9s1 HDVeyIj3rcPrhgn9z/LZoBoaB7FShLQiLZo/YCOxo/lGswpmawiBFkc9QK3zhy/pcJTY zYaXfkv7cpe1wvkcAEiCh0K8zvCZuLDePJysqvUIcf9IUsjgSRVkEuwngTPez2yaPf/H xK7A== X-Gm-Message-State: AJaThX6wVECF/s28MWGWzb2CYfKwbvDx0J7LfXQJeSypqHECxwDCcrR3 LPuHcb4G8eJDpVNDhvGcNKOZTFRI X-Google-Smtp-Source: AGs4zMYqqd3xwBkBwqBf9yHoQcIPzOztaY/xmEV4TatiK3QzBGbBO5U8LAf2bfDny7FVeRt/z5fmyA== X-Received: by 10.80.245.237 with SMTP id x42mr20744541edm.80.1512182085791; Fri, 01 Dec 2017 18:34:45 -0800 (PST) Received: from ip-172-20-0-14.eu-west-1.compute.internal (ec2-34-241-66-6.eu-west-1.compute.amazonaws.com. [34.241.66.6]) by smtp.gmail.com with ESMTPSA id c25sm5738926edb.52.2017.12.01.18.34.44 (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Fri, 01 Dec 2017 18:34:44 -0800 (PST) Content-Type: multipart/alternative; boundary="Apple-Mail=_D63F38FF-002D-494E-9458-37D6767F8A6C" Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) Subject: Re: Bad plan for ltree predicate <@ From: Roman Konoval In-Reply-To: <3515.1512164028@sss.pgh.pa.us> Date: Sat, 2 Dec 2017 03:34:43 +0100 Cc: pgsql-performance@lists.postgresql.org Message-Id: <0126E9AE-FBE8-4CB3-9DC0-6F0282D78C8B@gmail.com> References: <43899CF8-9D4F-4B06-8460-81CC2E0B68E5@gmail.com> <3515.1512164028@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.3124) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_D63F38FF-002D-494E-9458-37D6767F8A6C Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hi Tom, Thanks for your help. > On Dec 1, 2017, at 22:33, Tom Lane wrote: >=20 >=20 > The seqscan formulation of the query results in evaluating > this function afresh at most of the rows The function is defined as STABLE. I though that means that there is no = need to reevaluate it on every row as input parameter is the same for every = row and return value will be the same during the same query execution. Do I = understand incorrectly what STABLE means? Why is the function evaluated more than once? > , whereas shoving it into an > uncorrelated sub-select causes it to be evaluated only once. That, I > think, and not the seqscan-vs-indexscan aspect, is what makes the = bitmap > formulation go faster. Certainly you'd not expect that a bitmap scan = that > has to hit most of the rows anyway is going to win over a seqscan. >=20 > The fact that the planner goes for a bitmap scan in the second = formulation > is an artifact of the fact that it doesn't try to pre-evaluate = sub-selects > for selectivity estimation purposes, so you end up with a default = estimate > that says that the <@ condition only selects a small fraction of the = rows. > Not sure if we should try to change that or not. >=20 > I'd suggest setting the function's cost to 1000 or so and seeing if = that > doesn't improve matters. >=20 If I set function cost to 1000 I get slightly better plan but still 3.5 = more buffers are read when compared to bitmap scan which as you wrote = one would expect to be slower than seq scan. Here is the plan: = QUERY PLAN = --------------------------------------------------------------------------= --------------------------------------------------------------------------= ------------------------- Aggregate (cost=3D216438.81..216438.82 rows=3D1 width=3D0) (actual = time=3D1262.244..1262.245 rows=3D1 loops=3D1) Buffers: shared hit=3D169215 CTE trees -> Index Scan using document_head__id_path__gist__idx on = document_head d (cost=3D2.91..212787.85 rows=3D162265 width=3D74) = (actual time=3D0.115..727.119 rows=3D154854 loops=3D1) Index Cond: (id_path <@ = get_doc_path('78157c60-45bc-42c1-9aad-c5651995db5c'::character varying)) Filter: (((id)::text <> = '78157c60-45bc-42c1-9aad-c5651995db5c'::text) AND ((state)::text <> = 'DELETED'::text)) Rows Removed by Filter: 23 Buffers: shared hit=3D169215 -> CTE Scan on trees (cost=3D0.00..3245.30 rows=3D162265 width=3D0) = (actual time=3D0.119..1118.899 rows=3D154854 loops=3D1) Buffers: shared hit=3D169215 Total runtime: 1277.010 ms (11 rows) My understanding is that the optimal plan in this case should read less = data than bitmap scan by the amount of buffers hit by bitmap index scan.=20= It should read roughly all buffers of the table itself. Something like = the query with predicate using ltree literal instead of function = invocation: explain (analyze, buffers) with trees AS ( SELECT d.id, d.snapshot_id , NULL :: text[] AS permissions FROM document_head AS d WHERE (d.id_path <@ = '869c0187_51ae_4deb_a36f_0425fdafda6e.78157c60_45bc_42c1_9aad_c5651995db5c= '::ltree AND d.id !=3D '78157c60-45bc-42c1-9aad-c5651995db5c') AND = d.state !=3D 'DELETED' ) SELECT COUNT(*) FROM trees; = QUERY PLAN = --------------------------------------------------------------------------= --------------------------------------------------------------------------= ------------------------------------------------------------------------- Aggregate (cost=3D42114.02..42114.03 rows=3D1 width=3D0) (actual = time=3D997.427..997.427 rows=3D1 loops=3D1) Buffers: shared hit=3D35230 CTE trees -> Seq Scan on document_head d (cost=3D0.00..38463.06 rows=3D162265= width=3D74) (actual time=3D0.013..593.082 rows=3D154854 loops=3D1) Filter: ((id_path <@ = '869c0187_51ae_4deb_a36f_0425fdafda6e.78157c60_45bc_42c1_9aad_c5651995db5c= '::ltree) AND ((id)::text <> = '78157c60-45bc-42c1-9aad-c5651995db5c'::text) AND ((state)::text <> = 'DELETED'::text)) Rows Removed by Filter: 23357 Buffers: shared hit=3D35230 -> CTE Scan on trees (cost=3D0.00..3245.30 rows=3D162265 width=3D0) = (actual time=3D0.017..888.076 rows=3D154854 loops=3D1) Buffers: shared hit=3D35230 Total runtime: 1011.565 ms (10 rows) The question is if it possible to get plan like that using function or = some other way to get ltree value for given document_head.id value in = one query? As an alternative I can get ltree value with the separate query but this = would require 1. a round-trip to postgres 2. me to change isolation level to REPEATABLE READ to make sure that I = get consistent result=20 so I would like to avoid that. Regards, Roman Konoval= --Apple-Mail=_D63F38FF-002D-494E-9458-37D6767F8A6C Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii
Hi Tom,

Thanks for your = help.

On Dec 1, 2017, at 22:33, Tom Lane <tgl@sss.pgh.pa.us> = wrote:


 The seqscan formulation of the query = results in evaluating
this function afresh at most of the = rows

The = function is defined as STABLE. I though that means that there is no = need
 to reevaluate it on every row as input parameter is = the same for every row and
 return value will be the same = during the same query execution. Do I = understand
 incorrectly what STABLE means?
Why = is the function evaluated more than once?

, whereas shoving it into an
uncorrelated sub-select causes it to be evaluated only once. =  That, I
think, and not the seqscan-vs-indexscan = aspect, is what makes the bitmap
formulation go faster. =  Certainly you'd not expect that a bitmap scan that
has= to hit most of the rows anyway is going to win over a seqscan.

The fact that the planner goes for a bitmap = scan in the second formulation
is an artifact of the fact = that it doesn't try to pre-evaluate sub-selects
for = selectivity estimation purposes, so you end up with a default = estimate
that says that the <@ condition only selects a = small fraction of the rows.
Not sure if we should try to = change that or not.

I'd suggest setting the = function's cost to 1000 or so and seeing if that
doesn't = improve matters.


If I set function cost to 1000 I get slightly better = plan but still 3.5 more buffers are read when compared to bitmap scan = which as you wrote one would expect to be slower than seq scan.
Here is the plan:

                =                     =                     =                     =      QUERY PLAN
---------------------------------------------------------------= --------------------------------------------------------------------------= ------------------------------------
 Aggregate =  (cost=3D216438.81..216438.82 rows=3D1 width=3D0) (actual = time=3D1262.244..1262.245 rows=3D1 loops=3D1)
   Buffers: = shared hit=3D169215
   CTE trees
     ->  Index = Scan using document_head__id_path__gist__idx on document_head d =  (cost=3D2.91..212787.85 rows=3D162265 width=3D74) (actual = time=3D0.115..727.119 rows=3D154854 loops=3D1)
      =      Index Cond: (id_path <@ = get_doc_path('78157c60-45bc-42c1-9aad-c5651995db5c'::character = varying))
           Filter: (((id)::text = <> '78157c60-45bc-42c1-9aad-c5651995db5c'::text) AND = ((state)::text <> 'DELETED'::text))
      =      Rows Removed by Filter: 23
      =      Buffers: shared hit=3D169215
   -> =  CTE Scan on trees  (cost=3D0.00..3245.30 rows=3D162265 = width=3D0) (actual time=3D0.119..1118.899 rows=3D154854 = loops=3D1)
         Buffers: shared = hit=3D169215
 Total runtime: 1277.010 ms
(11 = rows)

My understanding is that the optimal plan in this case should = read less data than bitmap scan by the amount of buffers hit by bitmap = index scan. 
It should read roughly all = buffers of the table itself. Something like the query with predicate = using ltree literal instead of function invocation:

explain (analyze, = buffers)
with trees AS (
SELECT d.id, d.snapshot_id , NULL :: = text[] AS permissions
  FROM document_head AS d
  WHERE (d.id_path = <@ = '869c0187_51ae_4deb_a36f_0425fdafda6e.78157c60_45bc_42c1_9aad_c5651995db5c= '::ltree AND d.id !=3D '78157c60-45bc-42c1-9aad-c5651995db5c') AND = d.state !=3D 'DELETED'
)
SELECT COUNT(*) FROM trees;
          =                     =                     =                     =                     =                QUERY = PLAN
---------------------------------------------------------------= --------------------------------------------------------------------------= --------------------------------------------------------------------------= ----------
 Aggregate  (cost=3D42114.02..42114.03 rows=3D1 = width=3D0) (actual time=3D997.427..997.427 rows=3D1 = loops=3D1)
   Buffers: shared hit=3D35230
   CTE = trees
     ->  Seq Scan on document_head d =  (cost=3D0.00..38463.06 rows=3D162265 width=3D74) (actual = time=3D0.013..593.082 rows=3D154854 loops=3D1)
      =      Filter: ((id_path <@ = '869c0187_51ae_4deb_a36f_0425fdafda6e.78157c60_45bc_42c1_9aad_c5651995db5c= '::ltree) AND ((id)::text <> = '78157c60-45bc-42c1-9aad-c5651995db5c'::text) AND ((state)::text = <> 'DELETED'::text))
          =  Rows Removed by Filter: 23357
          =  Buffers: shared hit=3D35230
   ->  CTE Scan on = trees  (cost=3D0.00..3245.30 rows=3D162265 width=3D0) (actual = time=3D0.017..888.076 rows=3D154854 loops=3D1)
      =    Buffers: shared hit=3D35230
 Total runtime: 1011.565 = ms
(10 = rows)


The question is if it = possible to get plan like that using function or some other way to get = ltree value for given document_head.id value in one = query?

As an alternative I can get ltree value with the separate = query but this would require
1. a round-trip to = postgres
2. me to change isolation level to = REPEATABLE READ to make sure that I get consistent = result 
so I would like to avoid = that.

Regards,
Roman = Konoval
= --Apple-Mail=_D63F38FF-002D-494E-9458-37D6767F8A6C--