Re: Why is explain horribly optimistic for sorts?

nightly.

On Sat, 3 Mar 2001, Mitch Vincent wrote:

> You VACUUM and VACUUM ANALYZE regularly, right?
>
> -Mitch
>
> ----- Original Message -----
> From: "Ben" <bench@silentmedia.com>
> To: <pgsql-general@postgresql.org>
> Sent: Saturday, March 03, 2001 12:44 PM
> Subject: Why is explain horribly optimistic for sorts?
>
>
> > Hello all. We are logging our web server traffic to postgresql 7.0.3, and
> > that's working well. What's not working so well is retrieving our data in
> > reasonable times if I try to order it. When I run our queries through
> > explain, it *looks* like they will run in reasonable times, but in fact
> > they take several minutes. That's not so good. I'm wondering why explain
> > is so horribly wrong when it comes to sorts? For that matter, I'm
> > wondering why sorts take so incredibly long.
> >
> > Some background.....
> >
> > - We only have ~120,000 records.
> > - The relevant parts of the table are:
> >
> >             Table "jennyann"
> >    Attribute    |    Type     | Modifier
> > ----------------+-------------+----------
> >  ClientHost     | text        |
> >  LogTime        | timestamp   |
> >  target         | text        |
> >  host           | text        |
> > Indices: jennyan_host_key,
> >          jennyann_clienthost_key,
> >          jennyann_logtime_key,
> >          jennyann_target_key
> >
> > - All indices are normal btrees.
> > - ClientHost is (for the most part) an IP address.
> >
> > Here's what explain tells me:
> >
> > explain SELECT * FROM jennyann where target like '/music/%' order by
> "LogTime" limit 1000;
> > NOTICE:  QUERY PLAN:
> >
> > Sort  (cost=119.88..119.88 rows=2085 width=136)
> >   ->  Index Scan using jennyann_target_key on jennyann  (cost=0.00..4.94
> rows=2085 width=136)
> >
> >
> > A cost of 119 seems pretty good, and usually takes just a couple seconds
> > for other queries I've made. Unfortuantely, it's completely wrong. This
> > query takes several minutes to complete. If I drop the "order by" clause
> > then things get to be reasonable speeds, but I rather need that clause
> > there.
> >
> > Help? Please?
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>