From: Florian Lohoff <f@zz.de>
To: pgsql-performance@postgresql.org
Subject: union all and filter / index scan -> seq scan
Date: Thu, 21 May 2015 12:41:03 +0200
Message-ID: <20150521104103.GB10049@pax.zz.de> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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 = 42;
I can see an index scan happening on big_table. When i issue
something like
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 = 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
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.
I have now tried to wrap my head around the problem for 2 days and i am
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.
--
Florian Lohoff f@zz.de
We need to self-defense - GnuPG/PGP enable your email today!
Attachments:
signature.asc (application/pgp-signature, 827B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: f@zz.de
Subject: Re: union all and filter / index scan -> seq scan
In-Reply-To: <20150521104103.GB10049@pax.zz.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox