From: Rosser Schwarz <rosser.schwarz@gmail.com>
To: Shane|SkinnyCorp <shanew@skinnycorp.com>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: PLEASE GOD HELP US!
Date: Fri, 1 Oct 2004 15:16:29 -0500
Message-ID: <37d451f704100113166773828e@mail.gmail.com> (raw)
In-Reply-To: <007301c4a7eb$9353eac0$3402a8c0@shanepc>
References: <007301c4a7eb$9353eac0$3402a8c0@shanepc>
while you weren't looking, Shane | SkinnyCorp wrote:
> How else do you suggest I grab the 25 most recent
> threads posted?!?
select *
from thread_listing t
where t.status = 5
order by lastreply desc
limit 25
offset 0
The WHERE clause is there to limit the number of tuples you're looking
at. Without a WHERE clause, every tuple in the relation is pulled.
Compare the following queries and query plans, run against our
production database. The columns and types are different, but the
logic is identical. Note from the row counts in the first query that
this is a 5+m row table. Such queries generally return in fractions
of seconds for us.
tci=> explain select * from account.cust order by prodid = 153::bigint
desc, createddt desc limit 25 offset 0;
QUERY PLAN
-----------------------------------------------------------------------------
Limit (cost=1785296.85..1785296.91 rows=25 width=272)
-> Sort (cost=1785296.85..1797950.28 rows=5061375 width=272)
Sort Key: (prodid = 153::bigint), createddt
-> Seq Scan on cust (cost=0.00..207355.19 rows=5061375 width=272)
(4 rows)
tci=> explain select * from account.cust where prodid = 153::bigint
order by createddt desc limit 25 offset 0;
QUERY PLAN
---------------------------------------------------------------------------
Limit (cost=208299.77..208299.83 rows=25 width=272)
-> Sort (cost=208299.77..208334.13 rows=13743 width=272)
Sort Key: createddt
-> Seq Scan on cust (cost=0.00..207355.19 rows=13743 width=272)
Filter: (prodid = 153::bigint)
(5 rows)
The query with a WHERE clause has to look at and sort 13,743 rows; the
query without has to look at and sort all 5+m.
Which would you expect to be faster?
> Exactly.
/rls
--
:wq
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: rosser.schwarz@gmail.com, shanew@skinnycorp.com
Subject: Re: PLEASE GOD HELP US!
In-Reply-To: <37d451f704100113166773828e@mail.gmail.com>
References: <007301c4a7eb$9353eac0$3402a8c0@shanepc> <37d451f704100113166773828e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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