Re: [BUGS] BUG #6416: Expression index not used with UNION ALL queries
On 29/01/2012 22:33, Tom Lane wrote: > Matteo Beccati writes: >>> 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. > > Looks like I broke this back in November :-(. Fixed, thanks for the > report. Thanks a lot Tom! Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6416: Expression index not used with UNION ALL queries
Matteo Beccati writes: >> 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. Looks like I broke this back in November :-(. Fixed, thanks for the report. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6416: Expression index not used with UNION ALL queries
On 29/01/2012 16:06, p...@beccati.com wrote: > The following bug has been logged on the website: > > Bug reference: 6416 > Logged by: Matteo Beccati > Email address: p...@beccati.com > PostgreSQL version: 9.1.2 > Operating system: Debian Sqeeze > Description: > > 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: Sorry about the formatting issue. You will find it attached. Cheers -- Matteo Beccati Development & Consulting - http://www.beccati.com/ regression=# CREATE FUNCTION ab(in text, in text, out ab text) AS $$BEGIN ab := $1 || $2; END;$$ LANGUAGE plpgsql IMMUTABLE; CREATE FUNCTION regression=# CREATE table t1 (a text, b text); CREATE TABLE regression=# CREATE INDEX t1_ab_idx on t1 (ab(a, b)); CREATE INDEX regression=# CREATE table t2 (ab text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" CREATE TABLE regression=# INSERT INTO t1 VALUES ('a', 'b'); INSERT 0 1 regression=# INSERT INTO t2 VALUES ('ab'); INSERT 0 1 regression=# VACUUM ANALYZE ; VACUUM regression=# SET enable_seqscan = false; SET regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1 UNION ALL SELECT * FROM t2) t WHERE ab = 'ab'; QUERY PLAN - Result (cost=100.00..109.53 rows=2 width=18) (actual time=0.052..0.066 rows=2 loops=1) -> Append (cost=100.00..109.53 rows=2 width=18) (actual time=0.052..0.065 rows=2 loops=1) -> Seq Scan on t1 (cost=100.00..101.26 rows=1 width=32) (actual time=0.051..0.052 rows=1 loops=1) Filter: (ab(a, b) = 'ab'::text) -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.010..0.011 rows=1 loops=1) Index Cond: (ab = 'ab'::text) Total runtime: 0.106 ms (7 rows) regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1 UNION SELECT * FROM t2) t WHERE ab = 'ab'; QUERY PLAN Unique (cost=17.07..17.08 rows=2 width=4) (actual time=0.071..0.073 rows=1 loops=1) -> Sort (cost=17.07..17.07 rows=2 width=4) (actual time=0.070..0.070 rows=2 loops=1) Sort Key: (ab(t1.a, t1.b)) Sort Method: quicksort Memory: 25kB -> Append (cost=0.25..17.06 rows=2 width=4) (actual time=0.050..0.058 rows=2 loops=1) -> Index Scan using t1_ab_idx on t1 (cost=0.25..8.77 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=1) Index Cond: (ab(a, b) = 'ab'::text) -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.004..0.005 rows=1 loops=1) Index Cond: (ab = 'ab'::text) Total runtime: 0.116 ms (10 rows) regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1) t WHERE ab = 'ab'; QUERY PLAN -- Index Scan using t1_ab_idx on t1 (cost=0.25..8.77 rows=1 width=4) (actual time=0.030..0.032 rows=1 loops=1) Index Cond: (ab(a, b) = 'ab'::text) Total runtime: 0.048 ms (3 rows) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6416: Expression index not used with UNION ALL queries
The following bug has been logged on the website: Bug reference: 6416 Logged by: Matteo Beccati Email address: p...@beccati.com PostgreSQL version: 9.1.2 Operating system: Debian Sqeeze Description: 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=# CREATE FUNCTION ab(in text, in text, out ab text) AS $$BEGIN ab := $1 || $2; END;$$ LANGUAGE plpgsql IMMUTABLE; CREATE FUNCTION regression=# CREATE table t1 (a text, b text); CREATE TABLE regression=# CREATE INDEX t1_ab_idx on t1 (ab(a, b)); CREATE INDEX regression=# CREATE table t2 (ab text primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" CREATE TABLE regression=# INSERT INTO t1 VALUES ('a', 'b'); INSERT 0 1 regression=# INSERT INTO t2 VALUES ('ab'); INSERT 0 1 regression=# VACUUM ANALYZE ; VACUUM regression=# SET enable_seqscan = false; SET regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1 UNION ALL SELECT * FROM t2) t WHERE ab = 'ab'; QUERY PLAN - Result (cost=100.00..109.53 rows=2 width=18) (actual time=0.052..0.066 rows=2 loops=1) -> Append (cost=100.00..109.53 rows=2 width=18) (actual time=0.052..0.065 rows=2 loops=1) -> Seq Scan on t1 (cost=100.00..101.26 rows=1 width=32) (actual time=0.051..0.052 rows=1 loops=1) Filter: (ab(a, b) = 'ab'::text) -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.010..0.011 rows=1 loops=1) Index Cond: (ab = 'ab'::text) Total runtime: 0.106 ms (7 rows) regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1 UNION SELECT * FROM t2) t WHERE ab = 'ab'; QUERY PLAN Unique (cost=17.07..17.08 rows=2 width=4) (actual time=0.071..0.073 rows=1 loops=1) -> Sort (cost=17.07..17.07 rows=2 width=4) (actual time=0.070..0.070 rows=2 loops=1) Sort Key: (ab(t1.a, t1.b)) Sort Method: quicksort Memory: 25kB -> Append (cost=0.25..17.06 rows=2 width=4) (actual time=0.050..0.058 rows=2 loops=1) -> Index Scan using t1_ab_idx on t1 (cost=0.25..8.77 rows=1 width=4) (actual time=0.049..0.050 rows=1 loops=1) Index Cond: (ab(a, b) = 'ab'::text) -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=3) (actual time=0.004..0.005 rows=1 loops=1) Index Cond: (ab = 'ab'::text) Total runtime: 0.116 ms (10 rows) regression=# EXPLAIN ANALYZE SELECT * FROM (SELECT ab(a, b) AS ab FROM t1) t WHERE ab = 'ab'; QUERY PLAN -- Index Scan using t1_ab_idx on t1 (cost=0.25..8.77 rows=1 width=4) (actual time=0.030..0.032 rows=1 loops=1) Index Cond: (ab(a, b) = 'ab'::text) Total runtime: 0.048 ms (3 rows) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs