pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Which is more efficient?
2+ messages / 2 participants
[nested] [flat]

* Which is more efficient?
@ 2004-12-17 18:52 Mike G. <mike@thegodshalls.com>
  2004-12-18 02:55 ` Re: Which is more efficient? Christopher Browne <cbbrowne@acm.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Mike G. @ 2004-12-17 18:52 UTC (permalink / raw)
  To: pgsql-performance

Hi,

I have data that I am taking from 2 tables, pulling out specific columns and inserting into one table.

Is it more efficient to do:
a)  insert into x
    select z from y;
    insert into x
    select z from a;

b)  insert into x
    select z from y
    union all
    select z from a;

I have run both through explain.
a) 650ms
b) 741.57ms

According to the planner option a, select z from y takes 545.93 ms
Under option b select z from y takes 553.34 ms

Shouldn't the time predicted for the select z from y be the same?

I would believe b would be more efficient as the inserts could be done in a batch rather than individual transactions but the planner doesn't recognize that.  When I run option a through the planner I have to highlight each insert separately since the planner stops executing after the first ; it comes across.

Mike



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Which is more efficient?
  2004-12-17 18:52 Which is more efficient? Mike G. <mike@thegodshalls.com>
@ 2004-12-18 02:55 ` Christopher Browne <cbbrowne@acm.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Christopher Browne @ 2004-12-18 02:55 UTC (permalink / raw)
  To: pgsql-performance

A long time ago, in a galaxy far, far away, mike@thegodshalls.com ("Mike G.") wrote:
> Hi,
>
> I have data that I am taking from 2 tables, pulling out specific columns and inserting into one table.
>
> Is it more efficient to do:
> a)  insert into x
>     select z from y;
>     insert into x
>     select z from a;
>
> b)  insert into x
>     select z from y
>     union all
>     select z from a;
>
> I have run both through explain.
> a) 650ms
> b) 741.57ms
>
> According to the planner option a, select z from y takes 545.93 ms
> Under option b select z from y takes 553.34 ms
>
> Shouldn't the time predicted for the select z from y be the same?

No, these are approximations.  They can't be expected to be identical,
and as you can see there's no material difference, as 545.93 only
differs from 553.34 by 1.34%.

The point of EXPLAIN is to show the query _plans_ so you can evaluate
how sane they seem.  They're pretty well identical, so EXPLAIN's doing
what might be expected.

> I would believe b would be more efficient as the inserts could be
> done in a batch rather than individual transactions but the planner
> doesn't recognize that.  When I run option a through the planner I
> have to highlight each insert separately since the planner stops
> executing after the first ; it comes across.

The case where there would be a _material_ difference would be where
there were hardly any rows in either of the tables you're adding in,
and in that case, query planning becomes a significant cost, at which
point simpler is probably better.

If you do the queries in separate transactions, there's some addition
of cost of COMMIT involved, but if they can be kept in a single
transaction, the approaches oughtn't be materially different in cost,
and that's what you're finding.
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://www3.sympatico.ca/cbbrowne/x.html
MICROS~1:  Where do you  want to  go today?   Linux: Been  there, done
that.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-12-18 02:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-17 18:52 Which is more efficient? Mike G. <mike@thegodshalls.com>
2004-12-18 02:55 ` Christopher Browne <cbbrowne@acm.org>

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