From: Stephen Frost <sfrost@snowman.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Pavel Stehule <pavel.stehule@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: Thu, 27 Dec 2012 14:00:19 -0500
Message-ID: <20121227190019.GC16126@tamriel.snowman.net> (raw)
In-Reply-To: <CAMkU=1xZnkmeoTG9wrXshnSxWDCXGyO-tPRUYZ8YdgBptNcysA@mail.gmail.com>
References: <BLU002-W2777115565E1CBCF867857AB370@phx.gbl>
<CAM6mieLLD+WBx_NXubUihbDbHzb46tjxYWNWg_VgjV9omwOVcw@mail.gmail.com>
<BLU002-W73F8F8D02096BD549F2809AB370@phx.gbl>
<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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
* Jeff Janes (jeff.janes@gmail.com) wrote:
> If the main goal is to make it faster, I'd rather see all of plpgsql get
> faster, rather than just a special case of partitioning triggers. For
> example, right now a CASE <expression> statement with 100 branches is about
> the same speed as an equivalent list of 100 elsif. So it seems to be doing
> a linear search, when it could be doing a hash that should be a lot faster.
That's a nice thought, but I'm not sure that it'd really be practical.
CASE statements in plpgsql are completely general and really behave more
like an if/elsif tree than a C-style switch() statement or similar. For
one thing, the expression need not use the same variables, could be
complex multi-variable conditionals, etc.
Figuring out that you could build a dispatch table for a given CASE
statement and then building it, storing it, and remembering to use it,
wouldn't be cheap.
On the other hand, I've actually *wanted* a simpler syntax on occation.
I have no idea if there'd be a way to make it work, but this would be
kind of nice:
CASE OF x -- or whatever
WHEN 1 THEN blah blah
WHEN 2 THEN blah blah
WHEN 3 THEN blah blah
END
which would be possible to build into a dispatch table by looking at the
type of x and the literals used in the overall CASE statement. Even so,
there would likely be some number of WHEN conditions required before
it'd actually be more efficient to use, though perhaps getting rid of
the expression evaluation (if that'd be possible) would make up for it.
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, jeff.janes@gmail.com, pavel.stehule@gmail.com, charlesrg@outlook.com, ondrej.ivanic@gmail.com
Subject: Re: Performance on Bulk Insert to Partitioned Table
In-Reply-To: <20121227190019.GC16126@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