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

From Jan Ploski
Subject Re: Creating tons of tables to support a query
Date
Msg-id 13799530.1031529247563.JavaMail.jpl@remotejava
Whole thread Raw
In response to Creating tons of tables to support a query  (Jan Ploski <jpljpl@gmx.de>)
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


pgsql-general by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: Creating tons of tables to support a query
Next
From: Jan Ploski
Date:
Subject: Re: Creating tons of tables to support a query