I am testing a query what that has a sub-select. The query performance is very very poor as shown below due to the use of sequencial scans. The actual row count of both tables is also shown. It appears the row count shown by explain analyze does not match the actual count. Columns dstobj, srcobj & objectid are all indexed yet postgres insists on using seq scans. Vacuum analyze makes no difference. I am using 8.1.3 on linux.
This is a very simple query with relatively small amount of data and the query is taking 101482 ms. Queries with sub-selects on both tables individually is very fast (8 ms).
How do I prevent the use of seq scans?
capsa=# explain analyze select name from capsa.flatomfilesysentry where objectid in ( select dstobj from capsa.flatommemberrelation where srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=0.00..1386.45 rows=5809 width=14) (actual time=2.933..101467.463 rows=5841 loops=1)
Join Filter: ("outer".objectid = "inner".dstobj)
-> Seq Scan on flatomfilesysentry (cost=0.00..368.09 rows=5809 width=30) (actual time=0.007..23.451 rows=5844 loops=1)
-> Seq Scan on flatommemberrelation (cost=0.00..439.05 rows=5842 width=16) (actual time=0.007..11.790 rows=2922
loops=5844)
Filter: (srcobj = 'c1c7304a-1fe1-11db-8af7-001143214409'::capsa_sys.uuid)
Total runtime: 101482.256 ms
(6 rows)
capsa=# select count(*) from capsa.flatommemberrelation ;
count
-------
11932
(1 row)
capsa=# select count(*) from capsa.flatomfilesysentry ;
count
-------
5977
