Re: PLEASE GOD HELP US!

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