Re: Performance on Bulk Insert to Partitioned Table

2012/12/28 Vitalii Tymchyshyn <tivv00(at)gmail(dot)com>:
> Why so? Basic form "case lvalue when rvalue then out ... end" is much like
> switch.

Sorry, to be honest, I missed that distinction and didn't expect that to
work as-is, yet apparently it does. Does it currently perform the same
as an if/elsif tree or is it implemented to actually use a table lookup?

* Pavel Stehule (pavel(dot)stehule(at)gmail(dot)com) wrote:
> please, look to plpgsql source code. PL/pgSQL is too simply and has
> not own arithmetic unit - all is transformed to SELECTs, has not any
> optimization. But is really short and maintainable.

I was thinking we'd actually do this for all CASE statements, those in
plpgsql and those in regular SQL, if it's possible to do. Hopefully
it'd be possible to do easily in plpgsql once the SQL-level CASE is
done.

> These SELECTs are evaluated only when it is necessary - but it is
> evaluated by PostgreSQL expression executor - not by PL/pgSQL directly
> - PL/pgSQL cannot process constant by self.

Right, but I wonder if we could pass the entire CASE tree to the
executor, with essentially pointers to the code blocks which will be
executed, and get back a function which we can call over and over that
takes whatever the parameter is and returns the 'right' pointer?

> So any enhancing needs PL/pgSQL redesign and I am not sure, so this
> use case has accurate benefit, because expression bottleneck is only
> one part of partitioning triggers bottleneck. More - if you need
> really fast code, you can use own code in C - and it be 10x times
> faster than any optimized PL/pgSQL code. And using C triggers in
> PostgreSQL is not terrible work.

It's quite a bit of work for people who don't know C or are
(understandably) concerned about writing things which can easily
segfault the entire backend.

> Using plpgsql row triggers for partitioning is not good idea - it is
> just work around from my perspective, and we should to solve source of
> problem - missing native support.

I agree that native partitioning would certainly be nice. I was really
hoping that was going to happen for 9.3, but it seems unlikely now
(unless I've missed something).

Thanks,

Stephen