The following bug has been logged on the website:
Bug reference: 6416
Logged by: Matteo Beccati
Email address: php@beccati.com
PostgreSQL version: 9.1.2
Operating system: Debian Sqeeze
Description:=20=20=20=20=20=20=20=20
I've just noticed that an expression index I've created was not used with a
view contiaining a UNION ALL. Switching to UNION or querying the table
directly works as expected.
A self contained test case follows:
regression=3D# CREATE FUNCTION ab(in text, in text, out ab text) AS $$BEGIN=
ab
:=3D $1 || $2; END;$$ LANGUAGE plpgsql IMMUTABLE; CREATE FUNCTION regressio=
n=3D#
CREATE table t1 (a text, b text); CREATE TABLE regression=3D# CREATE INDEX
t1_ab_idx on t1 (ab(a, b)); CREATE INDEX regression=3D# CREATE table t2 (ab
text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index "t2_pkey" for table "t2" CREATE TABLE regression=3D# INSERT INTO t1
VALUES ('a', 'b'); INSERT 0 1 regression=3D# INSERT INTO t2 VALUES ('ab');
INSERT 0 1 regression=3D# VACUUM ANALYZE ; VACUUM regression=3D# SET
enable_seqscan =3D false; SET regression=3D# EXPLAIN ANALYZE SELECT * FROM
(SELECT ab(a, b) AS ab FROM t1 UNION ALL SELECT * FROM t2) t WHERE ab =3D
'ab'; QUERY PLAN
---------------------------------------------------------------------------=
--------------------------------------------------
Result (cost=3D10000000000.00..10000000009.53 rows=3D2 width=3D18) (actual
time=3D0.052..0.066 rows=3D2 loops=3D1) -> Append
(cost=3D10000000000.00..10000000009.53 rows=3D2 width=3D18) (actual
time=3D0.052..0.065 rows=3D2 loops=3D1) -> Seq Scan on t1
(cost=3D10000000000.00..10000000001.26 rows=3D1 width=3D32) (actual
time=3D0.051..0.052 rows=3D1 loops=3D1) Filter: (ab(a, b) =3D 'ab'::text) -=
> Index
Scan using t2_pkey on t2 (cost=3D0.00..8.27 rows=3D1 width=3D3) (actual
time=3D0.010..0.011 rows=3D1 loops=3D1) Index Cond: (ab =3D 'ab'::text) Tot=
al
runtime: 0.106 ms (7 rows) regression=3D# EXPLAIN ANALYZE SELECT * FROM
(SELECT ab(a, b) AS ab FROM t1 UNION SELECT * FROM t2) t WHERE ab =3D 'ab';
QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------------------------------
Unique (cost=3D17.07..17.08 rows=3D2 width=3D4) (actual time=3D0.071..0.073=
rows=3D1
loops=3D1) -> Sort (cost=3D17.07..17.07 rows=3D2 width=3D4) (actual
time=3D0.070..0.070 rows=3D2 loops=3D1) Sort Key: (ab(t1.a, t1.b)) Sort Met=
hod:
quicksort Memory: 25kB -> Append (cost=3D0.25..17.06 rows=3D2 width=3D4) (a=
ctual
time=3D0.050..0.058 rows=3D2 loops=3D1) -> Index Scan using t1_ab_idx on t1
(cost=3D0.25..8.77 rows=3D1 width=3D4) (actual time=3D0.049..0.050 rows=3D1=
loops=3D1)
Index Cond: (ab(a, b) =3D 'ab'::text) -> Index Scan using t2_pkey on t2
(cost=3D0.00..8.27 rows=3D1 width=3D3) (actual time=3D0.004..0.005 rows=3D1=
loops=3D1)
Index Cond: (ab =3D 'ab'::text) Total runtime: 0.116 ms (10 rows) regressio=
n=3D#
EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1) t WHERE ab =
=3D
'ab'; QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------------
Index Scan using t1_ab_idx on t1 (cost=3D0.25..8.77 rows=3D1 width=3D4) (ac=
tual
time=3D0.030..0.032 rows=3D1 loops=3D1) Index Cond: (ab(a, b) =3D 'ab'::tex=
t) Total
runtime: 0.048 ms (3 rows)
