Re: Creating tons of tables to support a query
От | Jan Ploski |
---|---|
Тема | Re: Creating tons of tables to support a query |
Дата | |
Msg-id | 13799530.1031529247563.JavaMail.jpl@remotejava обсуждение исходный текст |
Ответ на | Creating tons of tables to support a query (Jan Ploski <jpljpl@gmx.de>) |
Список | 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 по дате отправления: