On Monday, December 24, 2012, Charles Gomes wrote:
________________________________
>
> I think your performance bottleneck is almost certainly the dynamic
> SQL. Using C to generate that dynamic SQL isn't going to help much,
> because it is still the SQL engine that has to parse, plan, and execute
> it.
>
Jeff, I've changed the code from dynamic to:
CREATE OR REPLACE FUNCTION quotes_insert_trigger()
RETURNS trigger AS $$
DECLARE
r_date text;
BEGIN
r_date = to_char(new.received_time, 'YYYY_MM_DD');
case r_date
when '2012_09_10' then
insert into quotes_2012_09_10 values (NEW.*) using new;
return;
...
However I've got no speed improvement.
I need to keep two weeks worth of partitions at a time, that's why all the WHEN statements.
The 'using new' and return without argument are syntax errors.
When I do a model system with those fixed, I get about 2 fold improvement over the dynamic SQL performance. Even if your performance did not go up, did your CPU usage go down? Perhaps you have multiple bottlenecks all sitting at about the same place, and so tackling any one of them at a time doesn't get you anywhere.
How does both the dynamic and the CASE scale with the number of threads? I think you said you had something like 70 sessions, but only 8 CPUs. That probably will do bad things with contention, and I don't see how using more connections than CPUs is going to help you here. If the CASE starts out faster in single thread but then flat lines and the EXECUTE catches up, that suggests a different avenue of investigation than they are always the same.
Wish postgres could automate the partition process natively like the other sql db.
More automated would be nice (i.e. one operation to make both the check constraints and the trigger, so they can't get out of sync), but would not necessarily mean faster. I don't know what you mean about other db. Last time I looked at partitioning in mysql, it was only about breaking up the underlying storage into separate files (without regards to contents of the rows), so that is the same as what postgres does automatically. And in Oracle, their partitioning seemed about the same as postgres's as far as administrative tedium was concerned. I'm not familiar with how the MS product handles it, and maybe me experience with the other two are out of date.
Cheers,
Jeff