Re: [GENERAL] Question about partial functional indexes and the query planner

On Wed, Jun 11, 2014 at 7:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Jun 10, 2014 at 7:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Given the lack of previous complaints, I'm not sure this amounts to
> >> a back-patchable bug, but it does seem like something worth fixing
> >> going forward.
>
> > Agreed, although I'd be willing to see us slip it into 9.4. It's
> > doubtful that anyone will get upset if their query plans change
> > between beta1 and beta2, but the same cannot be said for released
> > branches.
>
> After further thought about this I realized that there's another category
> of proof possibilities that is pretty simple to add while we're at it.
> Namely, once we've found that both subexpressions of the two operator
> clauses are equal(), we can use btree opclass relationships to prove that,
> say, "x < y implies x <= y" or "x < y refutes x > y", independently of
> just what x and y are. (Well, they have to be immutable expressions, but
> we'd not get this far if they're not.) We already had pretty nearly all
> of the machinery for that, but again it was only used for proving cases
> involving comparisons to constants.
>
> A little bit of refactoring later, I offer the attached draft patch.
> I'm thinking this is probably more than we want to slip into 9.4
> at this point, but I'll commit it to HEAD soon if there are not
> objections.
>
> regards, tom lane
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
I applied Tom's patch to the latest HEAD
(e04a9ccd2ccd6e31cc4af6b08257a0a186d0fce8) and showed it to Brian. Looks to
solve the problem he originally reported

$ patch -p1 -i ../better-predicate-proofs-1.patch
(Stripping trailing CRs from patch.)
patching file src/backend/optimizer/util/predtest.c

$ /opt/pgsql_patch_review/bin/psql postgres
Timing is on.
Null display (null) is "«NULL»".
Expanded display (expanded) is used automatically.
psql (9.5devel)
Type "help" for help.

postgres=# CREATE OR REPLACE FUNCTION public.return_if_even(v_id integer)
returns integer
postgres-# LANGUAGE sql AS
postgres-# $$
postgres$# SELECT case when v_id % 2 = 1 then 0 else v_id end;
postgres$# $$;
CREATE FUNCTION
Time: 44.669 ms

postgres=# create table public.partial_functional_index_test as
postgres-# select id from generate_series(1,1000000) AS s(id);
SELECT 1000000
Time: 1037.993 ms

postgres=# create index partial_functional_idx ON
public.partial_functional_index_test
postgres-# USING btree ( public.return_if_even(id) )
postgres-# WHERE public.return_if_even(id) = id;
LOG: sending cancel to blocking autovacuum PID 12521
DETAIL: Process 12424 waits for ShareLock on relation 16385 of database
12217.
STATEMENT: create index partial_functional_idx ON
public.partial_functional_index_test
USING btree ( public.return_if_even(id) )
WHERE public.return_if_even(id) = id;
ERROR: canceling autovacuum task
CONTEXT: automatic analyze of table
"postgres.public.partial_functional_index_test"
CREATE INDEX
Time: 1658.245 ms

postgres=# explain analyze select count(1) from
public.partial_functional_index_test where public.return_if_even(id) = id;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4818.05..4818.06 rows=1 width=0) (actual
time=2503.851..2503.854 rows=1 loops=1)
-> Bitmap Heap Scan on partial_functional_index_test
(cost=82.67..4805.55 rows=5000 width=0) (actual time=43.724..1309.309
rows=500000 loops=1)
Recheck Cond: (CASE WHEN ((id % 2) = 1) THEN 0 ELSE id END = id)
Heap Blocks: exact=4425
-> Bitmap Index Scan on partial_functional_idx (cost=0.00..81.42
rows=5000 width=0) (actual time=42.961..42.961 rows=500000 loops=1)
Planning time: 4.245 ms
Execution time: 2505.281 ms
(7 rows)

Time: 2515.344 ms
postgres=# explain analyze select count(1) from
public.partial_functional_index_test where id = public.return_if_even(id);
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4818.05..4818.06 rows=1 width=0) (actual
time=2483.862..2483.866 rows=1 loops=1)
-> Bitmap Heap Scan on partial_functional_index_test
(cost=82.67..4805.55 rows=5000 width=0) (actual time=40.704..1282.955
rows=500000 loops=1)
Recheck Cond: (CASE WHEN ((id % 2) = 1) THEN 0 ELSE id END = id)
Heap Blocks: exact=4425
-> Bitmap Index Scan on partial_functional_idx (cost=0.00..81.42
rows=5000 width=0) (actual time=39.657..39.657 rows=500000 loops=1)
Planning time: 0.127 ms
Execution time: 2483.979 ms
(7 rows)

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com