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
