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! >
