pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedunion all and filter / index scan -> seq scan
3+ messages / 3 participants
[nested] [flat]
* union all and filter / index scan -> seq scan
@ 2015-05-21 10:41 Florian Lohoff <f@zz.de>
0 siblings, 2 replies; 3+ messages in thread
From: Florian Lohoff @ 2015-05-21 10:41 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union all and filter / index scan -> seq scan
@ 2015-05-21 15:09 Vitalii Tymchyshyn <vit@tym.im>
parent: Florian Lohoff <f@zz.de>
1 sibling, 0 replies; 3+ messages in thread
From: Vitalii Tymchyshyn @ 2015-05-21 15:09 UTC (permalink / raw)
To: Florian Lohoff <f@zz.de>; pgsql-performance
It looks pretty much like partitioning. You should check partitioning
recipes.
Чт, 21 трав. 2015 06:41 Florian Lohoff <f@zz.de> пише:
> 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!
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union all and filter / index scan -> seq scan
@ 2015-05-21 15:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Florian Lohoff <f@zz.de>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2015-05-21 15:28 UTC (permalink / raw)
To: Florian Lohoff <f@zz.de>; +Cc: pgsql-performance
Florian Lohoff <f@zz.de> writes:
> It seems the planner is not able to rewrite a union all
I do not see any problems with pushing indexable conditions down through a
UNION ALL when I try it. I speculate that either you are using a very old
9.1.x minor release, or the actual view is more complex than you've let on.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-05-21 15:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-21 10:41 union all and filter / index scan -> seq scan Florian Lohoff <f@zz.de>
2015-05-21 15:09 ` Vitalii Tymchyshyn <vit@tym.im>
2015-05-21 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
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