Обсуждение: union all and filter / index scan -> seq scan

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!

It looks pretty much like partitioning. You should check partitioning recipes.

Чт, 21 трав. 2015 06:41 Florian Lohoff <f@zz.de> пише:

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