pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Francisco Reyes <lists@natserv.com>
To: Janning Vygen <vygen@gmx.de>
Cc: HyunSung Jang <siche@siche.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: why my query is not using index??
Date: Mon, 11 Oct 2004 16:49:44 -0400 (EDT)
Message-ID: <20041011163756.M97379@zoraida.natserv.net> (raw)
In-Reply-To: <200410111425.02671.vygen@gmx.de>
References: <41639F3C.4050103@siche.net>
	<200410111425.02671.vygen@gmx.de>

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)



view thread (9+ messages)  latest in thread

Message-ID: <20041011163756.M97379@zoraida.natserv.net>
Permalink:  ../20041011163756.M97379@zoraida.natserv.net/
Also on:    postgresql.org/message-id/20041011163756.M97379@zoraida.natserv.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: lists@natserv.com, vygen@gmx.de, siche@siche.net
  Subject: Re: why my query is not using index??
  In-Reply-To: <20041011163756.M97379@zoraida.natserv.net>
  References: <41639F3C.4050103@siche.net> <200410111425.02671.vygen@gmx.de> <20041011163756.M97379@zoraida.natserv.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox