X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4F22632A14D for ; Mon, 11 Oct 2004 21:49:21 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 63552-07 for ; Mon, 11 Oct 2004 20:49:18 +0000 (GMT) Received: from mail1.acecape.com (mail1.acecape.com [66.114.74.12]) by svr1.postgresql.org (Postfix) with ESMTP id 9B7D232A10C for ; Mon, 11 Oct 2004 21:49:18 +0100 (BST) Received: from p65-147.acedsl.com (p65-147.acedsl.com [66.114.65.147]) by mail1.acecape.com (8.12.11/8.12.11) with ESMTP id i9BKn56d018997; Mon, 11 Oct 2004 16:49:05 -0400 Date: Mon, 11 Oct 2004 16:49:44 -0400 (EDT) From: Francisco Reyes X-X-Sender: fran@zoraida.natserv.net To: Janning Vygen Cc: HyunSung Jang , pgsql-performance@postgresql.org Subject: Re: why my query is not using index?? In-Reply-To: <200410111425.02671.vygen@gmx.de> Message-ID: <20041011163756.M97379@zoraida.natserv.net> References: <41639F3C.4050103@siche.net> <200410111425.02671.vygen@gmx.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/140 X-Sequence-Number: 8616 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)