Re: bad join performance

On Mon, 18 Aug 2003, pginfo wrote:

> Hi ,
> I am using pg 7.3.3 on RH 7.3,
> dual Athlon
> 1 GB RAM.
>
> I have 2 tables a_acc and a_vid_doc (all PK are int).
>
> sizes:
>
> select count(IDS) from a_acc;
> count
> ---------
> 1006772
>
> select count(IDS) from a_vid_doc;
> count
> -------
> 25
>
> I have problem with the join ot this tables.
> I tryed this examples:
>
> explain analyze select G.IDS from A_ACC G join A_VID_DOC VD
> ON(G.IDS_VID_DOC=VD.IDS) WHERE G.IDS = 1338673 ;

In general the best index on A_ACC for this kind of query might
be on on A_ACC(IDS, IDS_VID_DOC). That should allow you to search
by IDS value but still get a sorted order of IDS_VID_DOC to help
the join.