Re: Creating tons of tables to support a query - Mailing list pgsql-general

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