Re: [SQL] PG 8.2beta reordering working for this case?
Kyle Bateman <[EMAIL PROTECTED]> writes: > Is there a way to make the optimizer do this? Sorry, that's not happening for 8.2. Consider using a union all (not union) across the subledg_N tables directly and then joining to that. That boils down to being a partitioning case and I think probably will be covered by the 8.2 improvements. There isn't any understanding of how to commute joins and unions though ... (offhand I'm not even sure of the conditions under which such a thing would be safe). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] optimal insert
Hello experts, I have a database that contains three tables: create table a ( id serial primary key, ... -- some more fields not relevant for my question ); create table b ( id serial primary key, ... -- some more fields not relevant for my question ); create table a_b ( a int not null references a, b int not null references b ); Tables a and b have already been filled with lots of rows. Now my application needs to insert the relationship of a to b into table a_b and is currently doing it with inserts like the following: insert into a_b(a,b) values(1,100); insert into a_b(a,b) values(1,200); insert into a_b(a,b) values(1,54); insert into a_b(a,b) values(1,4577); So for a batch of inserts the value of a stays the same, while for by arbitrary values are inserted. Now I have wondered if PostreSQL offers a smarter way to insert those values? A solution can contains usage of some plpgsql code. -- ---> Dirk Jagdmann > http://cubic.org/~doj -> http://llg.cubic.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] optimal insert
On Sun, Oct 08, 2006 at 23:04:02 +0200, Dirk Jagdmann <[EMAIL PROTECTED]> wrote: > > insert into a_b(a,b) values(1,100); > insert into a_b(a,b) values(1,200); > insert into a_b(a,b) values(1,54); > insert into a_b(a,b) values(1,4577); > > So for a batch of inserts the value of a stays the same, while for by > arbitrary values are inserted. Now I have wondered if PostreSQL offers > a smarter way to insert those values? A solution can contains usage of > some plpgsql code. I don't think you are going to be able to save anything by 'a' being fixed unless there is a way to compute 'b', so that you can use a select statement. Just do the normal stuff you would when speeding up inserts. The biggest probably being wrapping them all up in one transaction. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PG 8.2beta reordering working for this case?
Tom Lane wrote: Kyle Bateman <[EMAIL PROTECTED]> writes: Is there a way to make the optimizer do this? Sorry, that's not happening for 8.2. Consider using a union all (not union) across the subledg_N tables directly and then joining to that. That boils down to being a partitioning case and I think probably will be covered by the 8.2 improvements. Yup, union all is much more efficient. It hadn't really occurred to me the difference between union and union all. But it makes sense to eliminate the need for a unique sort. The q3 query went from 10 seconds to 1 second with just the addition of union all in the general ledger. BTW, explain analyze still says 10 seconds of run time (and takes 10 seconds to run), but when I remove the explain analyze, the query runs in about a second. What's that all about? Also, I came up with the view shown in the attachment. It is still much faster than joining to the union-all ledger (40 ms). I'm not sure why because I'm not sure if explain analyze is telling me the real story (I see a sequential scan of the ledgers in there when it runs 10 seconds). I'm not sure what it's doing when it runs in 1 second. Kyle -- This view is a possible workaround for the problem drop view gen_ledg_pr; --explain analyze create view gen_ledg_pr as select lg.*, pr.anst_id from subview_A lg join proj_rel pr on pr.prog_id = lg.proj union all select lg.*, pr.anst_id from subview_B lg join proj_rel pr on pr.prog_id = lg.proj union all select lg.*, pr.anst_id from subview_C lg join proj_rel pr on pr.prog_id = lg.proj ; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
