pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEfficiency Question
2+ messages / 2 participants
[nested] [flat]
* Efficiency Question
@ 2002-09-09 00:13 Colin Fox <cfox@cfconsulting.ca>
2002-09-09 13:57 ` Re: Efficiency Question Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 2+ messages in thread
From: Colin Fox @ 2002-09-09 00:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Efficiency Question
2002-09-09 00:13 Efficiency Question Colin Fox <cfox@cfconsulting.ca>
@ 2002-09-09 13:57 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2002-09-09 13:57 UTC (permalink / raw)
To: Colin Fox <cfox@cfconsulting.ca>; +Cc: pgsql-sql
On Mon, Sep 09, 2002 at 00:13:04 +0000,
Colin Fox <cfox@cfconsulting.ca> wrote:
>
> 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.
If you have an index on creation_date an index scan can be used which
will make the second form run a lot faster if there are lots of records.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-09 13:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-09 00:13 Efficiency Question Colin Fox <cfox@cfconsulting.ca>
2002-09-09 13:57 ` Bruno Wolff III <bruno@wolff.to>
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