Re: Performance on Bulk Insert to Partitioned Table

[Vitalii Tymchyshyn <tivv00@xxxxxxxxx>]

There is switch-like sql case:

39.6.2.4. Simple CASE

CASE search-_expression_
    WHEN _expression_ [, _expression_ [ ... ]] THEN
      statements
  [ WHEN _expression_ [, _expression_ [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

It should work like C switch statement.

Also, for bulk insert, have you tried "for each statement" triggers instead of "for each row"?

This would look like a lot of inserts and would not be fast in single-row-insert case, but can give you benefit for huge inserts.

It should look like

insert into quotes_2012_09_10 select * from new where cast(new.received_time as date) = '2012-09-10' ;

insert into quotes_2012_09_11 select * from new where cast(new.received_time as date) = '2012-09-11' ;

...

2012/12/27 Stephen Frost <sfrost@xxxxxxxxxxx>

* Jeff Janes (jeff.janes@xxxxxxxxx) 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

--
Best regards,
 Vitalii Tymchyshyn