Lists: | pgsql-bugs |
---|
From: | php(at)beccati(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #6416: Expression index not used with UNION ALL queries |
Date: | 2012-01-29 15:06:04 |
Message-ID: | E1RrWKS-0006xF-3s@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 6416
Logged by: Matteo Beccati
Email address: php(at)beccati(dot)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=10000000000.00..10000000009.53 rows=2 width=18) (actual
time=0.052..0.066 rows=2 loops=1) -> Append
(cost=10000000000.00..10000000009.53 rows=2 width=18) (actual
time=0.052..0.065 rows=2 loops=1) -> Seq Scan on t1
(cost=10000000000.00..10000000001.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)
From: | Matteo Beccati <php(at)beccati(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6416: Expression index not used with UNION ALL queries |
Date: | 2012-01-29 15:16:06 |
Message-ID: | 4F2562B6.50509@beccati.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 29/01/2012 16:06, php(at)beccati(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 6416
> Logged by: Matteo Beccati
> Email address: php(at)beccati(dot)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/
Attachment | Content-Type | Size |
---|---|---|
bug6416.txt | text/plain | 3.0 KB |
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Matteo Beccati <php(at)beccati(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6416: Expression index not used with UNION ALL queries |
Date: | 2012-01-29 21:33:13 |
Message-ID: | 14415.1327872793@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Matteo Beccati <php(at)beccati(dot)com> 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
From: | Matteo Beccati <php(at)beccati(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6416: Expression index not used with UNION ALL queries |
Date: | 2012-01-30 09:52:44 |
Message-ID: | 4F26686C.2090706@beccati.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 29/01/2012 22:33, Tom Lane wrote:
> Matteo Beccati <php(at)beccati(dot)com> 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/