public inbox for [email protected]
help / color / mirror / Atom feedFrom: Frédéric Yhuel <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected] <[email protected]>
Cc: Jehan-Guillaume de Rorthais <[email protected]>
Cc: Christophe Courtois <[email protected]>
Cc: Laurenz Albe <[email protected]>
Subject: Re: Indexes on expressions with multiple columns and operators
Date: Wed, 17 Sep 2025 16:57:26 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
Thank you Laurenz and Tom! I'm going to quote Tom's email here:
On 9/17/25 16:41, Tom Lane wrote:
> =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <[email protected]> writes:
>> Hello, in the following, I don't understand why:
>> 1) the expression index isn't used in the first EXPLAIN
>
> The planner doesn't look for multi-clause matches of that sort.
> You could apply a little ju-jitsu perhaps:
>
> regression=# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE (ackid IS NULL AND crit = 'WARNING') is true;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------
> Index Scan using foo_expr_idx on foo (cost=0.29..8.39 rows=5 width=17) (actual time=0.013..0.016 rows=5.00 loops=1)
> Index Cond: (((ackid IS NULL) AND (crit = 'WARNING'::text)) = true)
> Index Searches: 1
> (3 rows)
>
Thanks, it works well indeed.
> but my own tendency would be to use a partial index rather than a
> boolean-valued index:
>
> regression=# CREATE INDEX foo_partial_idx ON foo (id) WHERE ackid IS NULL AND crit = 'WARNING';
> CREATE INDEX
> regression=# EXPLAIN (ANALYZE, SUMMARY OFF, BUFFERS OFF) SELECT * FROM foo WHERE ackid IS NULL AND crit = 'WARNING';
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------
> Index Scan using foo_partial_idx on foo (cost=0.13..107.18 rows=990 width=17) (actual time=0.010..0.014 rows=5.00 loops=1)
> Index Searches: 1
> (2 rows)
>
> The advantage of a partial index is you might be able to have the
> index entries themselves carry some other column(s), allowing
> more queries to be made into index-only scans. I put "id" here,
> which might or might not be of any use in this specific toy example.
>
Yes, Laurenz made a similar suggestion, but the problem is that I'm
mostly interested in the estimated number of output rows... because in
the real query, there's a very bad Hash Join above (the Nested Loop is
*much* faster).
>> 2) the number of estimated rows is completely off in the second EXPLAIN,
>> whereas the planner could easily use the statistics of foo_f_idx.
>
> Hmm, not sure about that. Again, boolean-valued indexes aren't
> something we've worked on too hard, but I don't see why that
> would affect this case.
>
OK, thanks anyway, I think the ju-jitsu mentioned above will do, even
though the application code will have to be patched.
view thread (26+ messages) latest in thread
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], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Indexes on expressions with multiple columns and operators
In-Reply-To: <[email protected]>
* 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