Re: why my query is not using index??

On Mon, 11 Oct 2004, Janning Vygen wrote:

> postgres uses a seq scan if its faster. In your case postgres seems to know
> that most of your rows have a date < 2004-01-01 and so doesn't need to
> consult the index if it has to read every page anyway. seq scan can be faster
> on small tables. try (in psql) "SET enable_seqscan TO off;" before running
> your query and see how postgres plans it without using seq scan.

I was about to post and saw this message.
I have a query that was using sequential scans. Upon turning seqscan to
off it changed to using the index. What does that mean?
The tables are under 5k records so I wonder if that is why the optimizer
is option, on it's default state, to do sequential scans.

I was also wondering if there is a relation between the sequential scans
and the fact that my entire query is a series of left joins:

(1)FROM Accounts
(2)LEFT JOIN Equity_Positions ON Accounts.Account_ID =
(3)Equity_Positions.Account_ID
(4)LEFT JOIN Equities USING( Equity_ID )
(5)LEFT JOIN Benchmarks USING( Benchmark_ID )
(6)LEFT JOIN Equity_Prices ON Equities.equity_id = Equity_Prices.equity_id
(7) AND Equity_Positions.Equity_Date = Equity_Prices.Date
(8)LEFT JOIN Benchmark_Positions ON Equities.Benchmark_ID =
(9)Benchmark_Positions.Benchmark_ID
(10) AND Equity_Positions.Equity_Date =
(11)Benchmark_Positions.Benchmark_Date
(12)WHERE Client_ID =32

When I saw the default explain I was surprised to see that indexes were
not been used. For example the join on lines 4,5 are exactly the primary
key of the tables yet a sequential scan was used.

The default explain was:

Sort (cost=382.01..382.15 rows=56 width=196)
Sort Key: accounts.account_group, accounts.account_name,
equities.equity_description, equity_positions.equity_date
-> Hash Left Join (cost=357.36..380.39 rows=56 width=196)
Hash Cond: (("outer".benchmark_id = "inner".benchmark_id) AND ("outer".equity_date = "inner".benchmark_date))
-> Hash Left Join (cost=353.41..375.46 rows=56 width=174)
Hash Cond: (("outer".equity_id = "inner".equity_id) AND ("outer".equity_date = "inner".date))
-> Hash Left Join (cost=292.22..296.90 rows=56 width=159)
Hash Cond: ("outer".benchmark_id = "inner".benchmark_id)
-> Merge Right Join (cost=290.40..294.51 rows=56 width=137)
Merge Cond: ("outer".equity_id = "inner".equity_id)
-> Sort (cost=47.19..48.83 rows=655 width=70)
Sort Key: equities.equity_id
-> Seq Scan on equities (cost=0.00..16.55 rows=655 width=70)
-> Sort (cost=243.21..243.35 rows=56 width=67)
Sort Key: equity_positions.equity_id
-> Nested Loop Left Join (cost=0.00..241.58 rows=56 width=67)
-> Seq Scan on accounts (cost=0.00..5.80 rows=3 width=44)
Filter: (client_id = 32)
-> Index Scan using positions_acct_equity_date on equity_positions (cost=0.00..78.30 rows=23 width=27)
Index Cond: ("outer".account_id = equity_positions.account_id)
-> Hash (cost=1.66..1.66 rows=66 width=22)
-> Seq Scan on benchmarks (cost=0.00..1.66 rows=66 width=22)
-> Hash (cost=50.79..50.79 rows=2079 width=23)
-> Seq Scan on equity_prices (cost=0.00..50.79 rows=2079 width=23)
-> Hash (cost=3.30..3.30 rows=130 width=30)
-> Seq Scan on benchmark_positions (cost=0.00..3.30 rows=130 width=30)

After set enable_seqscan to off;
It becomes

Sort (cost=490.82..490.96 rows=56 width=196)
Sort Key: accounts.account_group, accounts.account_name,
equities.equity_description, equity_positions.equity_date
-> Merge Left Join (cost=309.75..489.20 rows=56 width=196)
Merge Cond: ("outer".benchmark_id = "inner".benchmark_id)
Join Filter: ("outer".equity_date = "inner".benchmark_date)
-> Nested Loop Left Join (cost=309.75..644.88 rows=56 width=174)
-> Merge Left Join (cost=309.75..315.90 rows=56 width=159)
Merge Cond: ("outer".benchmark_id = "inner".benchmark_id)
-> Sort (cost=309.75..309.89 rows=56 width=137)
Sort Key: equities.benchmark_id
-> Merge Right Join (cost=254.43..308.12 rows=56 width=137)
Merge Cond: ("outer".equity_id = "inner".equity_id)
-> Index Scan using equities_pkey on equities (cost=0.00..51.21 rows=655 width=70)
-> Sort (cost=254.43..254.57 rows=56 width=67)
Sort Key: equity_positions.equity_id
-> Nested Loop Left Join (cost=0.00..252.81 rows=56 width=67)
-> Index Scan using accounts_pkey on accounts (cost=0.00..17.02 rows=3 width=44)
Filter: (client_id = 32)
-> Index Scan using positions_acct_equity_date on equity_positions (cost=0.00..78.30 rows=23 width=27)
Index Cond: ("outer".account_id = equity_positions.account_id)
-> Index Scan using benchmarks_pkey on benchmarks (cost=0.00..5.57 rows=66 width=22)
-> Index Scan using equity_prices_equity_date on equity_prices (cost=0.00..5.86 rows=1 width=23)
Index Cond: (("outer".equity_id = equity_prices.equity_id) AND ("outer".equity_date = equity_prices.date))
-> Index Scan using benchpositions_acct_equity_date on benchmark_positions (cost=0.00..10.82 rows=130 width=30)