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.30rows=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.30rows=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)