Re: Creating tons of tables to support a query
От | Jan Ploski |
---|---|
Тема | Re: Creating tons of tables to support a query |
Дата | |
Msg-id | 14629117.1031581356689.JavaMail.jpl@remotejava обсуждение исходный текст |
Ответ на | Creating tons of tables to support a query (Jan Ploski <jpljpl@gmx.de>) |
Ответы |
Re: Creating tons of tables to support a query
|
Список | pgsql-general |
On Mon, Sep 09, 2002 at 02:10:47PM +0100, Richard Huxton wrote: > On Monday 09 Sep 2002 11:52 am, Jan Ploski wrote: > > The problem is that (apparently?) the whole (dateSent, sectionID) index > > must be scanned in the second test, while the scnt_241 index simply > > contains no values and yields quick results. > > Have you considered using partial indexes? You can set up something like: > > CREATE INDEX msg_idx_9 ON message (dateSent) WHERE sectionID=9 > > For each section you have - this should allow for the indexing advantage > without the overhead of separate tables. This feature is non-standard AFAIK > and is covered in section 7.8 of the manual. Hello Richard, thanks for your hint. I did not know about this feature and it sounds like a perfect fit for the task. Having created the partial index for section 241 (the empty one), I run into another problem. EXPLAIN ANALYZE select msgnum from message where sectionID = 241 order by dateSent desc limit 1; Limit (cost=0.00..56.43 rows=1 width=12) (actual time=0.03..0.03 rows=0 loops=1) -> Index Scan Backward using message_pidx0_241 on message (cost=0.00..56.43 rows=0 width=12) (actual time=0.02..0.02rows=0 loops=1) Total runtime: 0.14 msec Looks fine. But: 500 iterations of the following code loop select msgnum from message into v_cnt where sectionID = 241 order by dateSent desc limit 1; end loop report execution time 00:00:00.03179, while 500 iterations of the following (which is closer to what I need): v_sid := 241; loop select msgnum from message into v_cnt where sectionID = v_sid order by dateSent desc limit 1; end loop take a whopping 00:00:31.442402. Needless to say, I don't have this problem with select msgnum from scnt_241 into v_cnt order by dateSent desc limit 1. But I'd really like to drop all these extra tables. How can I find out what is going on behind the scenes? Regards - JPL
В списке pgsql-general по дате отправления: