Thread: BUG #6662: Database do not push condition to subquery, test case for bug 6658

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

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

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