Subject: Re: Query became very slow after 9.6 -> 10 upgrade
To: Dmitry Shalashov <skaurus@gmail.com>,
 Alex Ignatov <a.ignatov@postgrespro.ru>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: 
 <CAKPeCUGAeHgoh5O=SvcQxREVkoX7UdeJUMj1F5=aBNvoTa+O8w@mail.gmail.com>
 <1379201d3639d$8b61bcd0$a2253670$@postgrespro.ru>
 <CAKPeCUHuxs8KU=4e9d=9HOjiX5=Y2x7NPNWiQZQ8N5yZpM8DPQ@mail.gmail.com>
 <137da01d363a0$603cc970$20b65c50$@postgrespro.ru>
 <CAKPeCUH9S7-Vz9SY4mjVxQ_4pHRvosVV+BZZXCkU3UUAykjOnA@mail.gmail.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <ab4918a9-bf22-2b80-ec7a-0fa484a8b9a8@2ndquadrant.com>
Date: Wed, 22 Nov 2017 16:07:26 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CAKPeCUH9S7-Vz9SY4mjVxQ_4pHRvosVV+BZZXCkU3UUAykjOnA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

IMHO the problems here are due to poor cardinality estimates.

For example in the first query, the problem is here:

    ->  Nested Loop  (cost=0.42..2.46 rows=1 width=59)
                     (actual time=2.431..91.330 rows=3173 loops=1)
        ->  CTE Scan on b  (cost=0.00..0.02 rows=1 width=40)
                           (actual time=2.407..23.115 rows=3173 loops=1)
        ->  Index Scan using domains_pkey on domains d
            (cost=0.42..2.44 rows=1 width=19)
            (actual time=0.018..0.018 rows=1 loops=3173)

That is, the database expects the CTE to return 1 row, but it returns
3173 of them, which makes the nested loop very inefficient.

Similarly for the other query, where this happens:

 Nested Loop  (cost=88.63..25617.31 rows=491 width=16)
              (actual time=3.512..733248.271 rows=1442797 loops=1)
   ->  HashAggregate  (cost=88.06..88.07 rows=1 width=4)
                      (actual time=3.380..13.561 rows=3043 loops=1)

That is, about 1:3000 difference in both cases.

Those estimation errors seem to be caused by a condition that is almost
impossible to estimate, because in both queries it does this:

    groups->0->>'provider' ~ '^something'

That is, it's a regexp on an expression. You might try creating an index
on the expression (which is the only way to add expression statistics),
and reformulate the condition as LIKE (which I believe we can estimate
better than regular expressions, but I haven't tried).

So something like

    CREATE INDEX ON adroom ((groups->0->>'provider'));

    WHERE groups->0->>'provider' LIKE 'something%';

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services