Re: Creating tons of tables to support a query

On Monday 09 Sep 2002 11:52 am, Jan Ploski wrote:
> 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.

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.

- Richard Huxton