Date: Thu, 21 May 2015 12:41:03 +0200
From: Florian Lohoff <f@zz.de>
To: pgsql-performance@postgresql.org
Subject: union all and filter / index scan -> seq scan 
Message-ID: <20150521104103.GB10049@pax.zz.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="jho1yZJdad60DJr+"
Content-Disposition: inline
Organization: rfc822 - pure communication
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--jho1yZJdad60DJr+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable


Hi,
i stumbled over something i cant seem to find a workaround. I create a view=
 like

create view v_test as
	select	a,b
	from	big_table
	union all
	select	a,b
	from	small_table;

When i now use the view like

	select * from v_test where a =3D 42;

I can see an index scan happening on big_table. When i issue
something like=20

	select * from v_test where a in ( select 42 );

or joining to another table i see that there will be seq scan on big
table. First the union will be executed and later the filter e.g. a in (
select 42 ) will be done on the huge result. My use case is that
big_table is >70mio entries growing fast and small_table is like 4
entries, growing little.  The filter e.g. "a in ( select 42 )" will
typically select 50-1000 entries of the 70mio. So i now create a union
with 70mio + 4 entries to then filter all with a =3D 42.

It seems the planner is not able to rewrite a union all e.g. the above
statement could be rewritten from:

	select	*
	from	(
		select	a,b
		from	big_table
		union all
		select	a,b
		from	small_table;
		) foo
	where	a in ( select 42 );

to=20

	select	*
	from	(
		select	a,b
		from	big_table
		where a in ( select 42 )
		union all
		select	a,b
		from	small_table
		where a in ( select 42 )
		) foo

which would then use an index scan not a seq scan and execution times
would be acceptable.=20

I have now tried to wrap my head around the problem for 2 days and i am=20
unable to find a workaround to using a union but the filter optimisation
is impossible with a view construct.

Flo
PS: Postgres 9.1 - I tried 9.4 on Debian/jessie with IIRC same results.
--=20
Florian Lohoff                                                 f@zz.de
     We need to self-defense - GnuPG/PGP enable your email today!

--jho1yZJdad60DJr+
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iQIVAwUBVV22PpDdQSDLCfIvAQplwg/8D/zZL0Of6aCKPSeVcCBSK7DVvTI1IuAa
ezU6/e9LkcgoQehncXeEVNhfPaVQbsfv7Rbcg3PbEY90ZX+P+tAxJ8skI/f99omW
5j/Vy8/TbVdwVSW823pDVMHHiJEugV65EZO3OWasYxZeWVQGP+ngZm5L6hJGvcSO
BbBonU1RQMNsz6Yx+LhBzoq8wlgQ1xXhdMoNsxBvojES59t7MrV1s8/etoYbN7Ek
MSznnzIgkvfaln/pTzCr7wYKk4Qu0VHeZgnyQtLbv/cb8uK51JhfkNK2EsQXHNIq
onKdzDMMMB3TI47tFQJ+z7CGYdhBKu6ZIAL1ZHb472f11Jbxrnf9wS1b+DzZh2gg
6hEc1ZqrSuqfgWsoK11qwWxymE6qutZ3h7OgH2ixHNlpYaeEeG3g6WZw7HuALu4E
5hf/80ItDyRqfhvM/dsHy8dxw3jQyTzfKBTStmh3B1nnrBBmoh8Kpoe/z+QITUQp
Dxbofq3Gpc5dKqaetNrow0a7nSESuU7bkF0v89UTOUX5VJbm3j59OJ3tZhwJibLI
hCM620B2V10+JF+mmAydpXSzCPEATyXq8a5bM2cYA3UzsxuS0xaP/e9F7oxYak8P
hbMFWMCGaFJyhXZu2eaQNkwTluYVRZiv2wC7lje7MWe3gtYqUZaBxv010HH4Zloe
OqcX/rnWo0M=
=zzlv
-----END PGP SIGNATURE-----

--jho1yZJdad60DJr+--