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