From: | maxim(dot)boguk(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #6662: Database do not push condition to subquery, test case for bug 6658 |
Date: | 2012-05-23 01:13:04 |
Message-ID: | E1SX08O-00085J-HA@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 6662
Logged by: Maxim Boguk
Email address: maxim(dot)boguk(at)gmail(dot)com
PostgreSQL version: 9.1.3
Operating system: Linux
Description:
I managed create simple self-contained test case for 6658.
create table test as select * from generate_series(1,100000) as g(val);
create index test_val_special on test((val || ''));
analyze test;
select count(*) from test;
--ok
--index scan
explain analyze
SELECT val
FROM test
WHERE (val || '')='something';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test_val_special on test (cost=0.01..8.29 rows=1 width=4)
(actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (((val)::text || ''::text) = 'something'::text)
Total runtime: 0.038 ms
--not ok
--seq scan on 9.1.3
explain analyze
SELECT val
FROM
(
SELECT
val,
(val || '') AS search_string
FROM test
) AS t1
WHERE search_string='something';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Subquery Scan on t1 (cost=0.00..3443.00 rows=500 width=4) (actual
time=47.076..47.076 rows=0 loops=1)
Filter: (t1.search_string = 'something'::text)
-> Seq Scan on test (cost=0.00..2193.00 rows=100000 width=4) (actual
time=0.012..34.949 rows=100000 loops=1)
Total runtime: 47.091 ms
--the same second query on 8.3.13
--plan correct
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using test_val_special on test (cost=0.01..8.29 rows=1 width=4)
(actual time=0.004..0.004 rows=0 loops=1)
Index Cond: (((val)::text || ''::text) = 'something'::text)
Total runtime: 0.018 ms
Kind Regards,
Maksym
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-05-23 01:46:04 | Re: BUG #6662: Database do not push condition to subquery, test case for bug 6658 |
Previous Message | Valentine Gogichashvili | 2012-05-23 00:11:30 | Re: BUG #6661: out-of-order XID insertion in KnownAssignedXids |