I've the follwoing two tables:
Table P3AT
AO CHAR(15)
AT Integer
AV VARCHAR(80)
AB LargeInteger
Table P3RL
RELS CHAR(15)
RELT CHAR(15)
SRCT Integer
and some other columns
Indices are on: P3AT.AO, P3AT.AT, P3RL.RELS, P3RL.RELT, P3RL.SRCT
Now I do the query:
a) SELECT AO,AT,AV FROM P3AT WHERE AO IN (SELECT RELT FROM P3RL WHERE RELS='9#####3#####RW#' AND (SRCT=1004025))
or via
b) SELECT AO,AT,AV FROM P3AT WHERE EXISTS (SELECT RELT FROM P3RL WHERE (RELT=AO) AND (RELS= ..) AND ...)
Both statements have the same explain result:
Seq Scan on p3at (cost=14458.84 rows=327480 width=28)SubPlan -> Index Scan using reltrgind om p3rl (cost=2.05 rows=1
width=12)
Both statements need about 3300 milliseconds to do the job ... and return the
three result rows ...
I gave it another sql-query and this works much better:
c) SELECT AO,AT,AV FROM P3AT,P3RL WHERE (P3AT.AO=P3RL.RELT) AND (RELS= ...) AND (SRCT= ...)
This gives the following explain result (and the one I would have expected):
NestedLoop (cost=4.1 rows=5 width=40) -> Index Scan using relsrcind on p3rl (cost=2.05 rows=1 width=12) -> Index Scan
usingatrownind on p3at (cost=2.05 rows=327480 width=28)
and needs only 5 ms !
Marten