From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marco Colli <collimarco91@gmail.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow "not in array" operation
Date: Tue, 12 Nov 2019 15:50:51 -0500
Message-ID: <28041.1573591851@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFvCgN4txnTT3Uev-YYOPaExPxX2xBYxq46BCohTUzWfWsmbgQ@mail.gmail.com>
References: <CAFvCgN73NJbSdtCFvcD3=Hh+xGipDkUk+H4uzubQXx2rncQJ+w@mail.gmail.com>
<CAHOFxGq3Udhpd9Y49JwCzqR_bUjCDzGsMX36V57ryzpbaG1hdg@mail.gmail.com>
<CAFvCgN6bQhf03jdakdk1eVMad8r=6nFV=cmy2v_sp0f8_kZ-bg@mail.gmail.com>
<CAHOFxGpt73epD-gYH32u9pOZJkvEO=iNstGJ+jkSkd59LuEtMQ@mail.gmail.com>
<CAFvCgN4txnTT3Uev-YYOPaExPxX2xBYxq46BCohTUzWfWsmbgQ@mail.gmail.com>
Marco Colli <collimarco91@gmail.com> writes:
> 3) Here's the query plan that I get after disabling the seq scan:
> Finalize Aggregate (cost=2183938.89..2183938.90 rows=1 width=8) (actual
> time=94972.253..94972.254 rows=1 loops=1)
So, this is slower than the seqscan, which means the planner made the
right choice.
You seem to be imagining that there's some way the index could be used
with the NOT clause, but there isn't. Indexable clauses are of the form
indexed_column indexable_operator constant
and there's no provision for a NOT in that. If we had a "not contained
in" array operator, the NOT could be folded to be of this syntactic form,
but it's highly unlikely that any index operator class would consider such
an operator to be a supported indexable operator. It doesn't lend itself
to searching an index.
So the planner is doing the best it can, which in this case is a
full-table scan.
A conceivable solution, if the tags array is a lot smaller than
the table as a whole and the table is fairly static, is that you could
make a btree index on the tags array and let the planner fall back
to an index-only scan that is just using the index as a cheaper
source of the array data. (This doesn't work for your existing GIST
index because GIST can't reconstruct the original arrays on-demand.)
I suspect though that this wouldn't win much, even if you disregard
the maintenance costs for the extra index. The really fundamental
problem here is that a large fraction of the table satisfies the
NOT-in condition, and no index is going to beat a seqscan by all that
much when that's true. Indexes are good at retrieving small portions
of tables.
regards, tom lane
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: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, collimarco91@gmail.com, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
Subject: Re: Slow "not in array" operation
In-Reply-To: <28041.1573591851@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox