[SQL] Efficiency Question

[Colin Fox]

Hi, all.

I'm putting together a small query that should return the most recent
entry from a table by date (I can't use an ID field as new entries may be
added with older dates). It's not crucial that this run at 100%
efficiency, but I'm interested in the results and/or discussion, as this
will probably relate to other larger queries later.

Two methods occur to me, and I don't have a large enough data set to get
any kind of idea of the actual response, and I'm not sure I understand the
explain plan.

Method 1 is like this:

select
*
from
motm
where
creation_date = (select max(creation_date) from motm);

Which requires a subselect and a max operator on a date field. Method two
is:

select
   *
from
   motm
order by
   creation_date desc
limit 1;

So in the first case I select the record that has the largest date. In the
second case, I order all the records, and then return only one.

Here's the explain for both:

--
Method 1:
NOTICE:  QUERY PLAN:

Merge Join  (cost=23.77..23.96 rows=1 width=60)
  InitPlan
->  Aggregate  (cost=22.50..22.50 rows=1 width=8)
  ->  Seq Scan on motm  (cost=0.00..20.00 rows=1000 width=8)
  ->  Sort  (cost=22.67..22.67 rows=10 width=20)
->  Seq Scan on motm m  (cost=0.00..22.50 rows=10 width=20)
  ->  Sort  (cost=1.11..1.11 rows=5 width=40)
->  Seq Scan on people p  (cost=0.00..1.05 rows=5 width=40)

EXPLAIN
--
Method 2:
NOTICE:  QUERY PLAN:

Limit  (cost=84.91..84.91 rows=1 width=68)
  ->  Sort  (cost=84.91..84.91 rows=50 width=68)
->  Merge Join  (cost=70.94..83.50 rows=50 width=68)
  ->  Sort  (cost=69.83..69.83 rows=1000 width=28)
->  Seq Scan on motm m  (cost=0.00..20.00 rows=1000 width=28)
  ->  Sort  (cost=1.11..1.11 rows=5 width=40)
->  Seq Scan on people p  (cost=0.00..1.05 rows=5 width=40)

EXPLAIN
--
According to the cost score, it seems that method 1 is faster, almost 4x!
Is that actually the case?

Opinions welcome. :)

Colin

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] Multiple counts

[Colin Fox]

Hi, all. 

I'm trying to calculate two count()s.

I have a campaigns table, a campaign_parts table and a people table.

Here is a simplified view of 3 tables I'm trying to use:

create table campaigns {id serial, company_id int, name varchar(20));
create table campaign_parts(id serial, campaign_id int, name varchar(20));
create table people (id serial, campaignpart_id int, name varchar(20));

(fk references ignored for brevity).


I'd like to create a query which shows how many campaign parts there are
for a given set of campaigns, and how many people signed up via a campaign
(which means a sum of all the signups under all the parts of that
campaign).

The naive way to do this would be something like:
select
camp.id,
camp.name,
count(cp.id) as numparts,
count(p.id) as numsignups
from
campaigns camp,
campaign_parts cp,
people p
where
camp.company_id =  and
cp.campaign_id = camp.id and
p.cpid = cp.id
group by
camp.id, camp.name;

(I know this is really naive, but I hope it shows what I'm trying to do).

However, there are some problems with this. First, if there are no
campaign parts, I'd like to show a 0, but then that campaign doesn't
show up at all. So I need to UNION it with another selection.

Also, the counting doesn't seem to be working. I'm getting a multiple of
the correct answer.

Please help!



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Left joins with multiple tables

[Colin Fox]

Hi, all.

I've got a bit of a problem here. I have 4 tables - people, a, b, c (not
the original names).

For each person in the people table, they may or may not have a record in
a, may or may not have a record in b, and may or may not have a record in
c.

Handling the first table (a) is easy:

select id, name
from people p left outer join a on a.person_id = p id;

But I'd like to be able to do something like:

select
id, name, a.field1, b.field2, c.field3
from
people p left outer join a on a.person_id = p id,
people p left outer join b on b.person_id = p.id,
people p left outer join c on c.person_id = p.id;

Naturally you can't repeat the 'people p' clause 3 times, but is there
some other syntax that would let me do this?

Thanks!
  cf



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster