Skip site navigation (1) Skip section navigation (2)

Re: Which is more efficient?

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Which is more efficient?
Date: 2004-12-18 02:55:27
Message-ID: m3pt181fj4.fsf@knuth.knuth.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-performance
A long time ago, in a galaxy far, far away, mike(at)thegodshalls(dot)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.

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-12-18 04:37:37
Subject: Re: Seqscan rather than Index
Previous:From: Steinar H. GundersonDate: 2004-12-17 23:55:48
Subject: Re: Seqscan rather than Index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group