Re: Creating tons of tables to support a query

On Mon, Sep 09, 2002 at 09:36:17AM +1000, Martijn van Oosterhout wrote:
> On Sun, Sep 08, 2002 at 11:58:44PM +0200, Jan Ploski wrote:
> > Hello,
> >
> > I am in particular wondering, why an index on message(sectionID, dateSent)
> > does not make these queries comparably fast:
> >
> > select msgnum from message where
> > sectionID = ? and
> > dateSent > ?
> > order by dateSent
> > limit 1;
> >
> > select msgnum from scnt_9 where
> > dateSent > ?
> > order by dateSent
> > limit 1;
> >
> > (scnt_9 is a lookup table which only creates msgnums for messages
> > with sectionID == 9)
> >
>
> Can you send the results of EXPLAIN ANALYZE for both those queries. Thanks.

Martijn,

I can't run EXPLAIN ANALYZE (using 7.1.3), but here are the results of
EXPLAIN:

Limit (cost=1677.74..1677.74 rows=1 width=10)
-> Sort (cost=1677.74..1677.74 rows=4449 width=10)
-> Seq Scan on message (cost=0.00..1408.13 rows=4449 width=10)

Limit (cost=0.00..0.05 rows=1 width=12)
-> Index Scan using scnt_idx_9 on scnt_9 (cost=0.00..234.47 rows=4661 width=

The fast query is obviously doing less work. Any ideas why?

Thank you -
JPL