From: Stephen Frost <sfrost@snowman.net>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: Jeff Janes <jeff.janes@gmail.com>
Cc: Charles Gomes <charlesrg@outlook.com>
Cc: Ondrej Ivanič <ondrej.ivanic@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Performance on Bulk Insert to Partitioned Table
Date: Fri, 28 Dec 2012 09:10:29 -0500
Message-ID: <20121228141029.GI16126@tamriel.snowman.net> (raw)
In-Reply-To: <CAFj8pRAU3OZrnxHdqyFO+OYRZfuAM+_2AYqKBedwSzY8tL9mWg@mail.gmail.com>
References: <CAMkU=1xbdrp-HsPQ5kJZDxDTWa8NnvLiYB9kXiCYN8X9WSGv8Q@mail.gmail.com>
<BLU002-W20060321FFADD1AA11E2021AB3B0@phx.gbl>
<CAMkU=1zJ7=DtRv8ikRH1Z8HcVwTKR+qw9yFF7z8mP__OL3D-9A@mail.gmail.com>
<BLU002-W1773678852199862DEB5D7CAB380@phx.gbl>
<CAMkU=1xZnkmeoTG9wrXshnSxWDCXGyO-tPRUYZ8YdgBptNcysA@mail.gmail.com>
<20121227190019.GC16126@tamriel.snowman.net>
<CABWW-d0vqjkt3zcdG0FOvZPNoO3ah18Np5hni-8H1N-hq4koNw@mail.gmail.com>
<20121228124141.GG16126@tamriel.snowman.net>
<CABWW-d25EyTzez=AbxB=kn+1w8aZOuh51-GLqWLBGNmRAizEtw@mail.gmail.com>
<CAFj8pRAU3OZrnxHdqyFO+OYRZfuAM+_2AYqKBedwSzY8tL9mWg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
2012/12/28 Vitalii Tymchyshyn <tivv00@gmail.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.stehule@gmail.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
Attachments:
signature.asc (application/pgp-signature, 197B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, pavel.stehule@gmail.com, tivv00@gmail.com, jeff.janes@gmail.com, charlesrg@outlook.com, ondrej.ivanic@gmail.com
Subject: Re: Performance on Bulk Insert to Partitioned Table
In-Reply-To: <20121228141029.GI16126@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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