Re: Creating tons of tables to support a query

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