Обсуждение: BUG #6662: Database do not push condition to subquery, test case for bug 6658
BUG #6662: Database do not push condition to subquery, test case for bug 6658
От
maxim.boguk@gmail.com
Дата:
The following bug has been logged on the website: Bug reference: 6662 Logged by: Maxim Boguk Email address: maxim.boguk@gmail.com PostgreSQL version: 9.1.3 Operating system: Linux Description:=20=20=20=20=20=20=20=20 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 || '')=3D'something'; QUERY PLAN ---------------------------------------------------------------------------= -------------------------------------------- Index Scan using test_val_special on test (cost=3D0.01..8.29 rows=3D1 wid= th=3D4) (actual time=3D0.011..0.011 rows=3D0 loops=3D1) Index Cond: (((val)::text || ''::text) =3D 'something'::text) Total runtime: 0.038 ms --not ok --seq scan on 9.1.3 explain analyze SELECT val=20 FROM ( SELECT val, (val || '') AS search_string FROM test ) AS t1 WHERE search_string=3D'something'; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------- Subquery Scan on t1 (cost=3D0.00..3443.00 rows=3D500 width=3D4) (actual time=3D47.076..47.076 rows=3D0 loops=3D1) Filter: (t1.search_string =3D 'something'::text) -> Seq Scan on test (cost=3D0.00..2193.00 rows=3D100000 width=3D4) (ac= tual time=3D0.012..34.949 rows=3D100000 loops=3D1) 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=3D0.01..8.29 rows=3D1 wid= th=3D4) (actual time=3D0.004..0.004 rows=3D0 loops=3D1) Index Cond: (((val)::text || ''::text) =3D 'something'::text) Total runtime: 0.018 ms Kind Regards, Maksym
maxim.boguk@gmail.com writes: > I managed create simple self-contained test case for 6658. This works fine in HEAD. The reason it doesn't work fine in 9.1 (or 9.0) is that in those branches, anytextcat() and textanycat() are marked volatile, for reasons that were good at the time but were superseded by later policy changes. So you have a subselect containing a volatile output expression, which prevents flattening of the subselect, so the restriction clause doesn't get pushed down to where it could be used with the index. For some history see http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c82d931dd180965a9a0c06acc764404f91de8170 http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=aab353a60b95aadc00f81da0c6d99bde696c4b75 http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=3db6524fe63f0598dcb2b307bb422bc126f2b15d If this is a big problem for you, you could safely adjust the provolatile marking of those functions by hand in 9.1. It might be better just to include an explicit cast to text, though, instead of relying on the assumption that the system will let you concat an integer directly to a text string. regards, tom lane
Re: BUG #6662: Database do not push condition to subquery, test case for bug 6658
От
Maxim Boguk
Дата:
Hi, Thank you very much for answer. Explicit casting resolved an issue. Just single question: if anytextcat() and textanycat() are marked volatile, why the database allows create index on supposedly to be volatile expression: create index test_val_special on test((val || '')); ? P On Wed, May 23, 2012 at 11:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > maxim.boguk@gmail.com writes: > > I managed create simple self-contained test case for 6658. > > This works fine in HEAD. The reason it doesn't work fine in 9.1 (or > 9.0) is that in those branches, anytextcat() and textanycat() are marked > volatile, for reasons that were good at the time but were superseded by > later policy changes. So you have a subselect containing a volatile > output expression, which prevents flattening of the subselect, so the > restriction clause doesn't get pushed down to where it could be used > with the index. For some history see > > http://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3Dc= 82d931dd180965a9a0c06acc764404f91de8170 > > http://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3Da= ab353a60b95aadc00f81da0c6d99bde696c4b75 > > http://git.postgresql.org/gitweb/?p=3Dpostgresql.git&a=3Dcommitdiff&h=3D3= db6524fe63f0598dcb2b307bb422bc126f2b15d > > If this is a big problem for you, you could safely adjust the > provolatile marking of those functions by hand in 9.1. It might be > better just to include an explicit cast to text, though, instead of > relying on the assumption that the system will let you concat an integer > directly to a text string. > > regards, tom lane > --=20 Maxim Boguk Senior Postgresql DBA. Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.boguk@gmail.com =D0=9C=D0=BE=D0=B9=D0=9A=D1=80=D1=83=D0=B3: http://mboguk.moikrug.ru/ "People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage."
Maxim Boguk <maxim.boguk@gmail.com> writes: > if anytextcat() and textanycat() are marked volatile, > why the database allows create index on supposedly to be volatile > expression: > create index test_val_special on test((val || '')); CREATE INDEX inlines anytextcat (which is just a SQL function) before making the volatility test, cf commit 5a86e5e19. The reason the subselect flattening code has difficulty with this case is that that happens before function inlining does, so the expression still looks volatile even though it really isn't. regards, tom lane
Re: BUG #6662: Database do not push condition to subquery, test case for bug 6658
От
Maxim Boguk
Дата:
On Wed, May 23, 2012 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Maxim Boguk <maxim.boguk@gmail.com> writes: > > if anytextcat() and textanycat() are marked volatile, > > why the database allows create index on supposedly to be volatile > > expression: > > create index test_val_special on test((val || '')); > > CREATE INDEX inlines anytextcat (which is just a SQL function) before > making the volatility test, cf commit 5a86e5e19. The reason the > subselect flattening code has difficulty with this case is that that > happens before function inlining does, so the expression still looks > volatile even though it really isn't. > > regards, tom lane > Thank you very much for all detailed explanation and all help. Kind Regards, Maksym