Re: Creating tons of tables to support a query

On Sun, Sep 08, 2002 at 07:49:32PM -0700, Stephan Szabo wrote:
> On Sun, 8 Sep 2002, Jan Ploski wrote:
>
> > 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;
>
> I don't think that'll use an index on (sectionID, dateSent) for the sort
> step. I think an index on (dateSent,sectionID) might be, however.

Stephan,

Indeed, my mistake. With an index on (dateSent,sectionID), the plan becomes:

Limit (cost=0.00..2.36 rows=1 width=10)
-> Index Scan using test_idx2 on message (cost=0.00..10482.08 rows=4449 width=10)

Alas, this does not help me further. I did two tests:

Test 1: Section 9 contained 5143 messages.
Test 2: Section 241 contained 0 messages.

The timing results (for 5000 queries) are:

1. Using index on message(dateSent, sectionID): 11 seconds
Using index on scnt_9(dateSent): 17 seconds

2. Using index on message(dateSent, sectionID): 320 seconds
Using index on scnt_241(dateSent): 2 seconds

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.

Since the auxiliary tables speed up things so much and behave well
for sections with few messages, I tend to believe that this is the
way to go for me. Two questions remain open: what kind of overheads
do I incur by creating that many tables (hundreds, maybe thousands
in the future)? And, second, since there is no support for pl/pgSQL
"execute select ... into" in 7.1.3, I need to collect results
inserted into a temporary table. Is this kind of "execute" statement
implemented in the newest version of PostgreSQL yet?

Take care -
JPL