On Wednesday, January 2, 2013, Tom Lane wrote:
> Jeff Janes <jeff.ja...@gmail.com> writes:
> > Using a RULE-based partitioning instead with row by row insertion, the
> > plancache changes slowed it down by 300%, and this patch doesn't change
> > that. But that seems to be down to the insertion getting planned
> > repeatedly, because it decides the custom plan is cheaper than the
> generic
> > plan. Whatever savings the custom plan may have are clearly less than
> the
> > cost of doing the planning repeatedly.
>
> That scenario doesn't sound like it has anything to do with the one being
> discussed in this thread. But what do you mean by "rule-based
> partitioning" exactly? A rule per se wouldn't result in a cached plan
> at all, let alone one with parameters, which would be necessary to
> trigger any use of the custom-cached-plan code path.
>
Right, it is not related to the dynamic SQL, but is to the plan-cache.
> Test cases are way more interesting than hand-wavy complaints.
>
Sorry, when exiled to the hinterlands I have more time to test various
things but not a good enough connectivity to describe them well. I'm
attaching the test case to load 1e5 rows into a very skinny table with 100
partitions using rules.
"origin" is from a few days ago, "origin_reduce_copies" is Heikki's patch,
and "origin_one_shot" is your now-committed patch. (unshown are
e6faf910d75027 and e6faf910d75027_prev, but that is where the regression
was introduced)
JJ /usr/local/pgsql_REL9_1_7/
Time: 64252.6907920837 ms
JJ origin/
Time: 186657.824039459 ms
JJ origin_reduce_copies/
Time: 185370.236873627 ms
JJ origin_one_shot/
Time: 189104.484081268 ms
The root problem is that it thinks the generic plan costs about 50% more
than the custom one. I don't know why it thinks that, or how much it is
worth chasing it down.
On the other hand, your patch does fix almost all of the 9.2.[012]
regression of using the following dynamic SQL trigger (instead of RULES) to
load into the same test case.
CREATE OR REPLACE FUNCTION foo_insert_trigger()
RETURNS trigger AS $$
DECLARE tablename varchar(24);
BEGIN
tablename = 'foo_' || new.partition;
EXECUTE 'INSERT INTO '|| tablename ||' VALUES (($1).*)' USING NEW ;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER foo_insert_trigger
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_insert_trigger();
Cheers,
Jeff
## The purpose of this program is to load the same files that \copy would
## load into a pgsql table, but do so one row at a time rather than in bulk.
## This could be useful to demonstrate the difference in loading efficiency between bulk
## and row by row, without having to use different formats for each.
## This makes no attempt to deal with escape characters like \t and \n the same way \copy does,
## so the loaded results will not be identical but that shouldn't matter if used only for
## benchmarking and not for actual production loading (and why would you use this for that purpose when
## \copy is available?)
## When loading into a 2 column unindexed untriggered table, Perl takes about half the CPU time and
## postgres about half. When loading to a table with triggers or indexs, Perl's slice becomes
## mostly immaterial.
use strict;
use warnings;
use DBI;
use Time::HiRes qw(time);
my $start=time();
my $dbi=DBI->connect('DBI:Pg:');
my ($columns) = $dbi->selectrow_array("select count(*) from information_schema.columns where table_name=?", undef, $ARGV[0]);
$columns > 0 or die "no such table '$ARGV[0]'";
## prepare an insert with as many placeholders as the table has columns
my $insert=$dbi->prepare("Insert into $ARGV[0] values (" . (join ',', map '?', 1..$columns) .')');
open my $fh, "<", $ARGV[1] or die "Couldn't open '$ARGV[1]': $!";
$dbi->begin_work();
while (<$fh>) { chomp;
my @x=split /\t/;
$insert->execute(@x);
};
$dbi->commit();
my $stop=time();
## make a timing output that look like the one psql \timing would generate
print "Time: ", 1000* ($stop-$start), " ms\n";
-- -- make rand.csv like below.
-- perl -le 'print join "\t", int(rand()*1e9),$_%100 foreach 1..1e5' > rand.csv
-- -- execute on different versions:
-- while (true) ; do for f in /usr/local/pgsql_REL9_1_7/ /usr/local/pgsql_REL9_2_2/ origin_94afbd5831fbc1926f/; do pg_ctl stop ; rm -r /tmp/data; $f/bin/initdb; $f/bin/pg_ctl start -w -o "--shared_buffers=512MB --checkpoint_segments=60 --shared_buffers=16MB"; createdb; echo "JJ $f"; psql -f rules_test.sql -X ; done ; done >& rules_test.sql.out
-- -- and pull results from the log file
-- tail -n 1000000 -f rules_test.sql.out |grep -P 'JJ|Time:' -a
drop table foo cascade;
create table foo ( x integer, partition integer);
--create index on foo (x);
create table foo_0 (like foo including indexes) inherits (foo);
create table foo_1 (like foo including indexes) inherits (foo);
create table foo_2 (like foo including indexes) inherits (foo);
create table foo_3 (like foo including indexes) inherits (foo);
create table foo_4 (like foo including indexes) inherits (foo);
create table foo_5 (like foo including indexes) inherits (foo);
create table foo_6 (like foo including indexes) inherits (foo);
create table foo_7 (like foo including indexes) inherits (foo);
create table foo_8 (like foo including indexes) inherits (foo);
create table foo_9 (like foo including indexes) inherits (foo);
create table foo_10 (like foo including indexes) inherits (foo);
create table foo_11 (like foo including indexes) inherits (foo);
create table foo_12 (like foo including indexes) inherits (foo);
create table foo_13 (like foo including indexes) inherits (foo);
create table foo_14 (like foo including indexes) inherits (foo);
create table foo_15 (like foo including indexes) inherits (foo);
create table foo_16 (like foo including indexes) inherits (foo);
create table foo_17 (like foo including indexes) inherits (foo);
create table foo_18 (like foo including indexes) inherits (foo);
create table foo_19 (like foo including indexes) inherits (foo);
create table foo_20 (like foo including indexes) inherits (foo);
create table foo_21 (like foo including indexes) inherits (foo);
create table foo_22 (like foo including indexes) inherits (foo);
create table foo_23 (like foo including indexes) inherits (foo);
create table foo_24 (like foo including indexes) inherits (foo);
create table foo_25 (like foo including indexes) inherits (foo);
create table foo_26 (like foo including indexes) inherits (foo);
create table foo_27 (like foo including indexes) inherits (foo);
create table foo_28 (like foo including indexes) inherits (foo);
create table foo_29 (like foo including indexes) inherits (foo);
create table foo_30 (like foo including indexes) inherits (foo);
create table foo_31 (like foo including indexes) inherits (foo);
create table foo_32 (like foo including indexes) inherits (foo);
create table foo_33 (like foo including indexes) inherits (foo);
create table foo_34 (like foo including indexes) inherits (foo);
create table foo_35 (like foo including indexes) inherits (foo);
create table foo_36 (like foo including indexes) inherits (foo);
create table foo_37 (like foo including indexes) inherits (foo);
create table foo_38 (like foo including indexes) inherits (foo);
create table foo_39 (like foo including indexes) inherits (foo);
create table foo_40 (like foo including indexes) inherits (foo);
create table foo_41 (like foo including indexes) inherits (foo);
create table foo_42 (like foo including indexes) inherits (foo);
create table foo_43 (like foo including indexes) inherits (foo);
create table foo_44 (like foo including indexes) inherits (foo);
create table foo_45 (like foo including indexes) inherits (foo);
create table foo_46 (like foo including indexes) inherits (foo);
create table foo_47 (like foo including indexes) inherits (foo);
create table foo_48 (like foo including indexes) inherits (foo);
create table foo_49 (like foo including indexes) inherits (foo);
create table foo_50 (like foo including indexes) inherits (foo);
create table foo_51 (like foo including indexes) inherits (foo);
create table foo_52 (like foo including indexes) inherits (foo);
create table foo_53 (like foo including indexes) inherits (foo);
create table foo_54 (like foo including indexes) inherits (foo);
create table foo_55 (like foo including indexes) inherits (foo);
create table foo_56 (like foo including indexes) inherits (foo);
create table foo_57 (like foo including indexes) inherits (foo);
create table foo_58 (like foo including indexes) inherits (foo);
create table foo_59 (like foo including indexes) inherits (foo);
create table foo_60 (like foo including indexes) inherits (foo);
create table foo_61 (like foo including indexes) inherits (foo);
create table foo_62 (like foo including indexes) inherits (foo);
create table foo_63 (like foo including indexes) inherits (foo);
create table foo_64 (like foo including indexes) inherits (foo);
create table foo_65 (like foo including indexes) inherits (foo);
create table foo_66 (like foo including indexes) inherits (foo);
create table foo_67 (like foo including indexes) inherits (foo);
create table foo_68 (like foo including indexes) inherits (foo);
create table foo_69 (like foo including indexes) inherits (foo);
create table foo_70 (like foo including indexes) inherits (foo);
create table foo_71 (like foo including indexes) inherits (foo);
create table foo_72 (like foo including indexes) inherits (foo);
create table foo_73 (like foo including indexes) inherits (foo);
create table foo_74 (like foo including indexes) inherits (foo);
create table foo_75 (like foo including indexes) inherits (foo);
create table foo_76 (like foo including indexes) inherits (foo);
create table foo_77 (like foo including indexes) inherits (foo);
create table foo_78 (like foo including indexes) inherits (foo);
create table foo_79 (like foo including indexes) inherits (foo);
create table foo_80 (like foo including indexes) inherits (foo);
create table foo_81 (like foo including indexes) inherits (foo);
create table foo_82 (like foo including indexes) inherits (foo);
create table foo_83 (like foo including indexes) inherits (foo);
create table foo_84 (like foo including indexes) inherits (foo);
create table foo_85 (like foo including indexes) inherits (foo);
create table foo_86 (like foo including indexes) inherits (foo);
create table foo_87 (like foo including indexes) inherits (foo);
create table foo_88 (like foo including indexes) inherits (foo);
create table foo_89 (like foo including indexes) inherits (foo);
create table foo_90 (like foo including indexes) inherits (foo);
create table foo_91 (like foo including indexes) inherits (foo);
create table foo_92 (like foo including indexes) inherits (foo);
create table foo_93 (like foo including indexes) inherits (foo);
create table foo_94 (like foo including indexes) inherits (foo);
create table foo_95 (like foo including indexes) inherits (foo);
create table foo_96 (like foo including indexes) inherits (foo);
create table foo_97 (like foo including indexes) inherits (foo);
create table foo_98 (like foo including indexes) inherits (foo);
create table foo_99 (like foo including indexes) inherits (foo);
create or replace rule foobar_0 as on insert to foo where new.partition=0 do instead insert into foo_0 values (new.*);
create or replace rule foobar_1 as on insert to foo where new.partition=1 do instead insert into foo_1 values (new.*);
create or replace rule foobar_2 as on insert to foo where new.partition=2 do instead insert into foo_2 values (new.*);
create or replace rule foobar_3 as on insert to foo where new.partition=3 do instead insert into foo_3 values (new.*);
create or replace rule foobar_4 as on insert to foo where new.partition=4 do instead insert into foo_4 values (new.*);
create or replace rule foobar_5 as on insert to foo where new.partition=5 do instead insert into foo_5 values (new.*);
create or replace rule foobar_6 as on insert to foo where new.partition=6 do instead insert into foo_6 values (new.*);
create or replace rule foobar_7 as on insert to foo where new.partition=7 do instead insert into foo_7 values (new.*);
create or replace rule foobar_8 as on insert to foo where new.partition=8 do instead insert into foo_8 values (new.*);
create or replace rule foobar_9 as on insert to foo where new.partition=9 do instead insert into foo_9 values (new.*);
create or replace rule foobar_10 as on insert to foo where new.partition=10 do instead insert into foo_10 values (new.*);
create or replace rule foobar_11 as on insert to foo where new.partition=11 do instead insert into foo_11 values (new.*);
create or replace rule foobar_12 as on insert to foo where new.partition=12 do instead insert into foo_12 values (new.*);
create or replace rule foobar_13 as on insert to foo where new.partition=13 do instead insert into foo_13 values (new.*);
create or replace rule foobar_14 as on insert to foo where new.partition=14 do instead insert into foo_14 values (new.*);
create or replace rule foobar_15 as on insert to foo where new.partition=15 do instead insert into foo_15 values (new.*);
create or replace rule foobar_16 as on insert to foo where new.partition=16 do instead insert into foo_16 values (new.*);
create or replace rule foobar_17 as on insert to foo where new.partition=17 do instead insert into foo_17 values (new.*);
create or replace rule foobar_18 as on insert to foo where new.partition=18 do instead insert into foo_18 values (new.*);
create or replace rule foobar_19 as on insert to foo where new.partition=19 do instead insert into foo_19 values (new.*);
create or replace rule foobar_20 as on insert to foo where new.partition=20 do instead insert into foo_20 values (new.*);
create or replace rule foobar_21 as on insert to foo where new.partition=21 do instead insert into foo_21 values (new.*);
create or replace rule foobar_22 as on insert to foo where new.partition=22 do instead insert into foo_22 values (new.*);
create or replace rule foobar_23 as on insert to foo where new.partition=23 do instead insert into foo_23 values (new.*);
create or replace rule foobar_24 as on insert to foo where new.partition=24 do instead insert into foo_24 values (new.*);
create or replace rule foobar_25 as on insert to foo where new.partition=25 do instead insert into foo_25 values (new.*);
create or replace rule foobar_26 as on insert to foo where new.partition=26 do instead insert into foo_26 values (new.*);
create or replace rule foobar_27 as on insert to foo where new.partition=27 do instead insert into foo_27 values (new.*);
create or replace rule foobar_28 as on insert to foo where new.partition=28 do instead insert into foo_28 values (new.*);
create or replace rule foobar_29 as on insert to foo where new.partition=29 do instead insert into foo_29 values (new.*);
create or replace rule foobar_30 as on insert to foo where new.partition=30 do instead insert into foo_30 values (new.*);
create or replace rule foobar_31 as on insert to foo where new.partition=31 do instead insert into foo_31 values (new.*);
create or replace rule foobar_32 as on insert to foo where new.partition=32 do instead insert into foo_32 values (new.*);
create or replace rule foobar_33 as on insert to foo where new.partition=33 do instead insert into foo_33 values (new.*);
create or replace rule foobar_34 as on insert to foo where new.partition=34 do instead insert into foo_34 values (new.*);
create or replace rule foobar_35 as on insert to foo where new.partition=35 do instead insert into foo_35 values (new.*);
create or replace rule foobar_36 as on insert to foo where new.partition=36 do instead insert into foo_36 values (new.*);
create or replace rule foobar_37 as on insert to foo where new.partition=37 do instead insert into foo_37 values (new.*);
create or replace rule foobar_38 as on insert to foo where new.partition=38 do instead insert into foo_38 values (new.*);
create or replace rule foobar_39 as on insert to foo where new.partition=39 do instead insert into foo_39 values (new.*);
create or replace rule foobar_40 as on insert to foo where new.partition=40 do instead insert into foo_40 values (new.*);
create or replace rule foobar_41 as on insert to foo where new.partition=41 do instead insert into foo_41 values (new.*);
create or replace rule foobar_42 as on insert to foo where new.partition=42 do instead insert into foo_42 values (new.*);
create or replace rule foobar_43 as on insert to foo where new.partition=43 do instead insert into foo_43 values (new.*);
create or replace rule foobar_44 as on insert to foo where new.partition=44 do instead insert into foo_44 values (new.*);
create or replace rule foobar_45 as on insert to foo where new.partition=45 do instead insert into foo_45 values (new.*);
create or replace rule foobar_46 as on insert to foo where new.partition=46 do instead insert into foo_46 values (new.*);
create or replace rule foobar_47 as on insert to foo where new.partition=47 do instead insert into foo_47 values (new.*);
create or replace rule foobar_48 as on insert to foo where new.partition=48 do instead insert into foo_48 values (new.*);
create or replace rule foobar_49 as on insert to foo where new.partition=49 do instead insert into foo_49 values (new.*);
create or replace rule foobar_50 as on insert to foo where new.partition=50 do instead insert into foo_50 values (new.*);
create or replace rule foobar_51 as on insert to foo where new.partition=51 do instead insert into foo_51 values (new.*);
create or replace rule foobar_52 as on insert to foo where new.partition=52 do instead insert into foo_52 values (new.*);
create or replace rule foobar_53 as on insert to foo where new.partition=53 do instead insert into foo_53 values (new.*);
create or replace rule foobar_54 as on insert to foo where new.partition=54 do instead insert into foo_54 values (new.*);
create or replace rule foobar_55 as on insert to foo where new.partition=55 do instead insert into foo_55 values (new.*);
create or replace rule foobar_56 as on insert to foo where new.partition=56 do instead insert into foo_56 values (new.*);
create or replace rule foobar_57 as on insert to foo where new.partition=57 do instead insert into foo_57 values (new.*);
create or replace rule foobar_58 as on insert to foo where new.partition=58 do instead insert into foo_58 values (new.*);
create or replace rule foobar_59 as on insert to foo where new.partition=59 do instead insert into foo_59 values (new.*);
create or replace rule foobar_60 as on insert to foo where new.partition=60 do instead insert into foo_60 values (new.*);
create or replace rule foobar_61 as on insert to foo where new.partition=61 do instead insert into foo_61 values (new.*);
create or replace rule foobar_62 as on insert to foo where new.partition=62 do instead insert into foo_62 values (new.*);
create or replace rule foobar_63 as on insert to foo where new.partition=63 do instead insert into foo_63 values (new.*);
create or replace rule foobar_64 as on insert to foo where new.partition=64 do instead insert into foo_64 values (new.*);
create or replace rule foobar_65 as on insert to foo where new.partition=65 do instead insert into foo_65 values (new.*);
create or replace rule foobar_66 as on insert to foo where new.partition=66 do instead insert into foo_66 values (new.*);
create or replace rule foobar_67 as on insert to foo where new.partition=67 do instead insert into foo_67 values (new.*);
create or replace rule foobar_68 as on insert to foo where new.partition=68 do instead insert into foo_68 values (new.*);
create or replace rule foobar_69 as on insert to foo where new.partition=69 do instead insert into foo_69 values (new.*);
create or replace rule foobar_70 as on insert to foo where new.partition=70 do instead insert into foo_70 values (new.*);
create or replace rule foobar_71 as on insert to foo where new.partition=71 do instead insert into foo_71 values (new.*);
create or replace rule foobar_72 as on insert to foo where new.partition=72 do instead insert into foo_72 values (new.*);
create or replace rule foobar_73 as on insert to foo where new.partition=73 do instead insert into foo_73 values (new.*);
create or replace rule foobar_74 as on insert to foo where new.partition=74 do instead insert into foo_74 values (new.*);
create or replace rule foobar_75 as on insert to foo where new.partition=75 do instead insert into foo_75 values (new.*);
create or replace rule foobar_76 as on insert to foo where new.partition=76 do instead insert into foo_76 values (new.*);
create or replace rule foobar_77 as on insert to foo where new.partition=77 do instead insert into foo_77 values (new.*);
create or replace rule foobar_78 as on insert to foo where new.partition=78 do instead insert into foo_78 values (new.*);
create or replace rule foobar_79 as on insert to foo where new.partition=79 do instead insert into foo_79 values (new.*);
create or replace rule foobar_80 as on insert to foo where new.partition=80 do instead insert into foo_80 values (new.*);
create or replace rule foobar_81 as on insert to foo where new.partition=81 do instead insert into foo_81 values (new.*);
create or replace rule foobar_82 as on insert to foo where new.partition=82 do instead insert into foo_82 values (new.*);
create or replace rule foobar_83 as on insert to foo where new.partition=83 do instead insert into foo_83 values (new.*);
create or replace rule foobar_84 as on insert to foo where new.partition=84 do instead insert into foo_84 values (new.*);
create or replace rule foobar_85 as on insert to foo where new.partition=85 do instead insert into foo_85 values (new.*);
create or replace rule foobar_86 as on insert to foo where new.partition=86 do instead insert into foo_86 values (new.*);
create or replace rule foobar_87 as on insert to foo where new.partition=87 do instead insert into foo_87 values (new.*);
create or replace rule foobar_88 as on insert to foo where new.partition=88 do instead insert into foo_88 values (new.*);
create or replace rule foobar_89 as on insert to foo where new.partition=89 do instead insert into foo_89 values (new.*);
create or replace rule foobar_90 as on insert to foo where new.partition=90 do instead insert into foo_90 values (new.*);
create or replace rule foobar_91 as on insert to foo where new.partition=91 do instead insert into foo_91 values (new.*);
create or replace rule foobar_92 as on insert to foo where new.partition=92 do instead insert into foo_92 values (new.*);
create or replace rule foobar_93 as on insert to foo where new.partition=93 do instead insert into foo_93 values (new.*);
create or replace rule foobar_94 as on insert to foo where new.partition=94 do instead insert into foo_94 values (new.*);
create or replace rule foobar_95 as on insert to foo where new.partition=95 do instead insert into foo_95 values (new.*);
create or replace rule foobar_96 as on insert to foo where new.partition=96 do instead insert into foo_96 values (new.*);
create or replace rule foobar_97 as on insert to foo where new.partition=97 do instead insert into foo_97 values (new.*);
create or replace rule foobar_98 as on insert to foo where new.partition=98 do instead insert into foo_98 values (new.*);
create or replace rule foobar_99 as on insert to foo where new.partition=99 do instead insert into foo_99 values (new.*);
truncate foo;
\! perl insert.pl foo rand.csv
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
