From: | Vitalii Tymchyshyn <vit(at)tym(dot)im> |
---|---|
To: | Florian Lohoff <f(at)zz(dot)de>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: union all and filter / index scan -> seq scan |
Date: | 2015-05-21 15:09:32 |
Message-ID: | CABWW-d2BFDSc4T74J7P2a-w_cUqRyZ3UHKmHmYGNWB4D96Phrw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
It looks pretty much like partitioning. You should check partitioning
recipes.
Чт, 21 трав. 2015 06:41 Florian Lohoff <f(at)zz(dot)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(at)zz(dot)de
> We need to self-defense - GnuPG/PGP enable your email today!
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-05-21 15:28:16 | Re: union all and filter / index scan -> seq scan |
Previous Message | Florian Lohoff | 2015-05-21 10:41:03 | union all and filter / index scan -> seq scan |