• 締切済み

POSTGRESQL 検索スピードが遅い

テーブル結合検索、インデックス使えない原因を Aテーブルは200000件のデータがあり、Bテーブルは4000000件のデータがあります。 テーブルA（ヘッダ） A001　　NUMBER(10) NOT NULL, A002　　NUMBER(8) NOT NULL, A003　　CHARACTER(2) NOT NULL, ... PRIMARY KEY(A001) INDEX A1(A002, A003) テーブルB（明細） B001　　NUMBER(10) NOT NULL, B002　　NUMBER(3) NOT NULL, B003　　NUMBER(7) NOT NULL, ... PRIMARY KEY(B001,B002) INDEX A1(B001) SELECT A.A001, B.B003 FROM A, B WHERE A.A001 = B.B001 AND A.A002 >= 20090728 AND A.A002 <= 20090801 AND A.A003 = '01' 実行計画を見ると、Bテーブルのインデックスが使わなかった。 同じＳＱＬで、条件だけ変わると、Bテーブルのインデックスが使った。 SELECT A.A001, B.B003 FROM A, B WHERE A.A001 = B.B001 AND A.A002 >= 20090728 AND A.A002 <= 20090731 AND A.A003 = '01' 原因を知りたいです。 また、他のＨＰからテーブルの結合の説明がありました、 直積結合の回避 * 直積結合は、結合対象の2つのテーブルの全レコードの組み合わせを戻す処理 * SQLが複雑で、結合条件や絞込み条件に漏れがあると直積結合が選択されるかも tableA = 1000 件、 tableB = 2 万件 の直積結合 1,000 * 20,000 = 20,000,000 2000 万件処理されてしまう が、結果が正しいと気付かない事も。 データ量が増加すれば、致命的な性能劣化に 参考ＵＲＬ： http://www.slideshare.net/kwappa/20090107-postgre-sqlsql-presentation 上記のこと教えていただきたいんです、お願いします。

みんなの回答

  • yamada59
  • ベストアンサー率74% (29/39)
回答No.4

根本的な問題の解決には回答番号: No.2 で marimari01 さんが言うようにデータ型を適切なものに変更する必要があります。 ただ、テーブル定義の変更が困難な場合には統計情報のエントリ数を増やして対応できる場合もあります。 PostgreSQL では、データの分布状況などの統計情報をもとに実行計画を作成しますが、データの分布が著しく偏っている場合にはデフォルトのエントリ数 (8.3 までは 10、8.4 では 100) では適切な実行計画を作成できない場合があります。 統計情報のエントリ数を増やすには ALTER TABLE ... SET STATISTICS を実行します。以下の例ではエントリ数を 200 に増やしています。 ALTER TABLE ar06kakh ALTER ar06005 SET STATISTICS 200; エントリ数を増やして ANALYZE を実行後に実行計画を確認して調整するといいと思います。 エントリ数を増やすと ANALYZE の実行時間が長くなるので注意してください。エントリ数を増やしても効果がないようであれば、インデックスが使用されやすくなるようにプランナコスト定数などのパラメータを調整するか、データ型を適切なものに変更するしかないですね。

noname#246547
noname#246547
回答No.3

先の回答でNUMBERと記述しましたが、NUMERICと読み替えてください

noname#246547
noname#246547
回答No.2

最初のexplainで ＞and a.ar06005 >= 20060830 and a.ar06005 <= 20060831 ＞and a.ar06009 = '01' で絞り込めるレコード数の推定件数が ＞" -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..93.60 rows=46 width=344) (actual time=0.046..43.635 rows=336 より、46件と見積もっています 次に、 ＞and a.ar06005 >= 20060830 and a.ar06005 <= 20060901 ＞and a.ar06009 = '01' では、 ＞" -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..5958.64 rows=3278 width=344) (actual time=0.229..189.109 rows=500 loops=1)" 上記の結果から3278件と見積もっています ここで 3278/46=71.2倍ですね 次に 20060901-20060830=71です ar06005が1増加すると46row増えると見積もっているのではないでしょうか？ そして、 ＞"Total runtime: 4188.728 ms" に71倍すると 297399.688msとなり ＞"Total runtime: 166043.673 ms" よりコストが増える つまり、Nested Loop よりHash Join の方がコストが少ないと判断したのではないでしょうか？ 結論ですが、 20060830 や 20060901（列：ar06005） はNUMBER型でDATE型ではないため、 オプティマイザは、20060832～20060900の間にもデータが有ると判断して、実行計画を立てていると思います 言い方を変えると、このテーブルのデータは著しく偏っているといえます NUMBERを型DATE型に変えるとNestedLoopを利用してくれると思います

noname#246547
noname#246547
回答No.1

データが著しく偏っていると、 インデックスを使用するより、全件検索したほうが早いとオプティマイザが 判断することはあります 8/1のデータが異常に多いとかね 実際の実行計画を貼り付けてもらえると、 わかりやすいかも・・・

zb_jiang
質問者

補足

explain analyze select a.* from ar06kakh a , ar07kakm b where a.ar06001 = b.ar07001 and a.ar06005 >= 20060830 and a.ar06005 <= 20060831 and a.ar06009 = '01' 上記の条件で実行計画をやると、下記の結果が出てきます。 "Nested Loop (cost=0.00..12432.40 rows=1022 width=344) (actual time=37.445..4186.452 rows=6636 loops=1)" " -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..93.60 rows=46 width=344) (actual time=0.046..43.635 rows=336 loops=1)" " Index Cond: ((ar06005 >= 20060830::numeric) AND (ar06005 <= 20060831::numeric) AND (ar06009 = '01'::bpchar))" " -> Index Scan using ar07_key17 on ar07kakm b (cost=0.00..257.28 rows=876 width=8) (actual time=5.605..12.306 rows=20 loops=336)" " Index Cond: (b.ar07001 = a.ar06001)" "Total runtime: 4188.728 ms" explain analyze select a.* from ar06kakh a , ar07kakm b where a.ar06001 = b.ar07001 and a.ar06005 >= 20060830 and a.ar06005 <= 20060901 and a.ar06009 = '01' 同じSQL、検索期間だけ変わると、下記のような結果が出てきます。 "Hash Join (cost=6147.61..362891.32 rows=72833 width=344) (actual time=543.516..166040.292 rows=9678 loops=1)" " Hash Cond: (b.ar07001 = a.ar06001)" " -> Seq Scan on ar07kakm b (cost=0.00..302154.25 rows=4192225 width=8) (actual time=29.452..106880.430 rows=4192225 loops=1)" " -> Hash (cost=5958.64..5958.64 rows=3278 width=344) (actual time=203.584..203.584 rows=500 loops=1)" " -> Index Scan using ar06_key13 on ar06kakh a (cost=0.00..5958.64 rows=3278 width=344) (actual time=0.229..189.109 rows=500 loops=1)" " Index Cond: ((ar06005 >= 20060830::numeric) AND (ar06005 <= 20060901::numeric) AND (ar06009 = '01'::bpchar))" "Total runtime: 166043.673 ms" 因みに、索引は下記のようです。 ar06_key0：AR06001 … ar06_key13：AR06005、AR06009 ar07_key0：AR07001、AR07002 … ar07_key17：AR07001 また、AR06005のデータは日付を「YYYYMMDD」の形式のNUMERICです。 AR06005の日付毎にデータの件数は大体同じぐらいです（100～200件）。 AR06001のAR07KAKMのデータが1～300件ぐらいです。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. PostgreSQL

関連するQ&A

  • ORACLEでwhere句の検索順序

    Oracle9i windows2000です。 以下のようなテーブルがあります。 table_a ----------------------- id　　　NUMBER(10,0) NOT NULL, sort　　　NUMBER(10,0) NOT NULL, name　　　VARCHAR(10), text　　　VARCHAR(255) この条件で、以下のふたつのSELECT文を発行した時、パフォーマンスが良いのはどちらですか？ Oracleでは後ろから検索されると聞いたことがあるのですが本当でしょうか？ ※idにプライマリキー、 id,sortにインデックスが貼ってあります。 (1)SELECT text FROM table_a WHERE id = 1 AND　sort = 2 AND　name = 'a' (2)SELECT text FROM table_a WHERE name = 'a' AND　sort = 2 AND　id = 1

  • 表の結合（性能）

    ２つのテーブルを（内部）結合させる場合、 INNER JOINによる結合と WHERE句に結合条件を記述する２通りの方法が あると思いますが、どちらが早いのでしょうか？ なお、Oracle9i、ルールベースにて構築されています。 テーブルの内容、SQL文は下記のとおりです。 ご教授よろしくお願いします。 ■表 A 項目A-１ ・・・PRIMARY KEY1、INDEX1 項目A-２ ・・・PRIMARY KEY2、INDEX2 項目A-３ ・・・PRIMARY KEY3 項目A-４ ■表 B 項目B-１ ・・・PRIMARY KEY1、INDEX1 項目B-２ ・・・PRIMARY KEY2、INDEX2 項目B-３ ・・・PRIMARY KEY3 項目B-４ ※項目A-１と項目B-１にて結合。 　項目A-２が"01"と等しいレコード。 表Aのレコード件数＞表Bのレコード件数 【WHERE句にて結合】 SELECT * FROM A,B where A.項目１ = B.項目１ and A.項目２ = '01' 【INNER JOINにて結合】 SELECT * FROM A INNER JOIN A ON A.項目１ = B.項目１ AND A.項目２ = '01'

  • SQL Server2005 2008の文字列検索のスピードについて

    SQL Server2005 2008の文字列検索のスピードについて 初歩的な質問で申し訳ありません。 下記のような文字列比較にて処理結果取得時間に差がある理由が知りたいのですが？ ご存知の方がいらっしゃいましたらご回答願います。 下記の処理パターンで調査した結果「パターン１」の処理結果は早いのですが、 「パターン２と３」は、処理結果取得までに数分かかります。 （抽出結果は２０件程度） SELECT COUNT(*) FROM [dbo].[注文データ] パターン１ WHERE [注文データ].[納品日] > (SELECT CONVERT(VARCHAR,DATEADD(dd,-10,GETDATE()),112) FROM DUAL) パターン２ WHERE [注文データ].[納品日] > CONVERT(VARCHAR,DATEADD(dd,-10,GETDATE()),112) パターン３ WHERE [注文データ].[納品日] > '20100601' CREATE TABLE [dbo].[注文データ]( [品番] [varchar](10) NOT NULL, [品名] [varchar](60) NOT NULL, [発注数量] [decimal](9, 2) NOT NULL, [納品日] [char](8) NOT NULL, PRIMARY KEY CLUSTERED ( [納品日] ASC, [品番] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] CREATE TABLE [dbo].[DUAL]( [DUMMY_COL] [varchar](1) NOT NULL, CONSTRAINT [PK_DUAL] PRIMARY KEY CLUSTERED ( [DUMMY_COL] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY] ■「注文データ」テーブルは過去１年間の累積で 　　１２０万件程度の注文履歴ファイルとする。 ■「DUAL」テーブルは「DUMMY_COL」に「0」をセットした 　　１件のデータとする。 よろしくお願い致します。　m(_ _)m

  • PostgreSQL、テーブル比較で片方のテーブルにないレコードのみ表

    PostgreSQL、テーブル比較で片方のテーブルにないレコードのみ表示する方法 こんばんは、お世話になっております。 タイトル通りの質問です。 以下のテーブルがあったとします。 Aテーブル No.|data1|data2 ---+-----+---- 1 |a |10 2 |b |11 3 |c |12 4 |d |13 5 |e |14 Bテーブル No.|data1|data2 ---+-----+---- 1 |a |10 2 |b |11 3 |c |12 4 |d |13 5 |e |14 1 |f |10 ２つのテーブルがあり、結合後条件の条件で 『＝ｆのような直接的な指定はしないで』 Bテーブルのdataが『f』となっているもののみ表示させたいです。 WHEREの条件に 『Aテーブル.data1 <> Bテーブル.data1』 等を記述をしましたが理想の結果は得られませんでした。 postgresのバージョンは8.2、 pgAdminのバージョンは1.6.3 です。 何か方法をご存知の方がいらっしゃいましたら回答いただきたく思います。 不足な点があれば指摘してください。補足させていただきます。 以上です、よろしくお願いします

  • 外部結合について

    外部結合というのは、表と表を結合するという事で FROM句に書くべきことと思うのですが、 なぜWHERE句(カラム毎に指定)で指定するのでしょうか？ 例えば、 テーブルA No|Kind|Name| ------------- １|１　|Ａ　| １|２　|Ｂ　| ２|１　|Ｃ　| ２|２　|Ｄ　| テーブルB No|Kind|Data| ------------- １|１　|１０| 外部結合で SELECT A.No, A.Kind, A.Name, B.Data FROM A,B WHERE A.No = B.No(+) AND A.Kind = B.Kind(+) とすると No|Kind|Name|Data| ------------------ １|１　|Ａ　|１０| １|２　|Ｂ　|NULL| ２|１　|Ｃ　|NULL| ２|２　|Ｄ　|NULL| となります。 A.NoがB.NoになくてもＯＫで、Kindは 完全に一致しないとだめという意味で SELECT A.No, A.Kind, A.Name, B.Data FROM A,B WHERE A.No = B.No(+) AND A.Kind = B.Kind とした場合、 No|Kind|Name|Data| ------------------ １|１　|Ａ　|１０| ２|１　|Ｃ　|NULL| となるのであれば納得いくのですが、 結果は↓なるようです。 No|Kind|Name|Data| -------------------------- １|１　|Ａ　|１０| (+)を一つも付けてない場合と 同じ動作になるようです。 テーブルAとテーブルBを結合するとき、 「外部結合する場合はWHERE句で指定する選択の条件全てに(+)を付ける」 「外部結合をしない場合はWHERE句で指定する選択の条件全てに(+)を付けない」 であれば、カラム毎に外部結合演算子を指定する意味が無いと思うのですが。 どなたか、カラム毎に外部結合演算子を指定する意味をご教授下さい。 宜しくお願いします。

  • WHERE句の条件の記述の順序

    PRIMARY KEYとINDEXがテーブルに設定されている場合、 検索条件に記述する順番はどのようになるのでしょうか？ 下記のテーブルがあり、SELECT文をつくろうと 考えています。 テーブル：foo 項目　　PRIMARY KEY　　INDEX ----------------------------------- a 1 b 2 1 c 3 d 2 (1)PRIMARY KEYを優先してWHERE句の順番を決める↓ SELECT * FROM foo WHERE a = "AAA" AND b = "BBB" AND c = "CCC" AND d = "DDD" (2) それともINDEXが設定されている項目を先に記述する↓ SELECT * FROM foo WHERE b = "BBB" AND d = "DDD" 　 AND a = "AAA" AND c = "CCC" (1)と(2)ではどちらの性能がよいのでしょうか？

  • Access結合後の短いテキスト型のインデックス

    Accessの検索にて、テーブルA LEFT JOIN テーブルB で外部結合し、 WHERE句でテーブルBの短いテキスト型を抽出条件にすると、検索が遅くなります。 【テーブル定義】 テーブルA（ ・年月日（日付/時刻、重複ありインデックス） ・コード（短テキスト、重複ありインデックス） ・属性あ（短テキスト、重複ありインデックス） ・属性い（短テキスト、重複ありインデックス） ） テーブルB（ ・コード（短テキスト、主KEY） ・属性う（短テキスト、重複ありインデックス） ・属性え（数値、重複ありインデックス） ） 【件数】 テーブルA：15万件 テーブルB：500件 =====SQLここから===== SELECT　* FROM　テーブルA LEFT JOIN　テーブルB ON　テーブルA．コード　＝　テーブルB．コード WHERE 属性あ　IS　NULL AND　属性い　＝　'あああ' AND　属性う　＝　'アアア' AND　属性え　＝　1 GROUP　BY 年月日、 属性う =====SQLここまで===== この検索SQLは遅い(1分30秒くらい)のですが 『AND　属性う　＝　'アアア'』を削除すると 10秒くらいに速くなります。 ”属性う”　のインデックスが効いてないように見えるのですが どのようにチューニングしたら速くなるでしょうか？ エクセルからLAN越しにDAO接続してSQL実行してます。 AccessはOffice365(バージョン1902)です。

  • 検索条件が複数の場合のインデックスの張り方

    Mysql5.0 + ASP.NETで開発中です。 サーバーはWindows2003サーバーです。 とある検索サイトを作っていますが、1テーブルのフィールド数が80くらいあります。 また、レコード数は常時100万件程度です。 このテーブルの検索を行うときに、ユーザーが任意の検索条件を設定できるような画面なのですが、実際に検索に使用されるフィールド数は最大で10です。 例えば、where a = 999 and b = 999 や where a = 999 and c = 999 and f = 999　や where b = 999 and d = 999 and f = 999 and g = 999 など、where句で使用されるフィールドがユーザーの指定により常に異なります。(999は任意の値です) ORDER BYに使用されるフィールド数は3です。 現状ではインデックスは張っていないため、かなり検索速度が遅いため、インデックスを張りたいのですが、どのような張り方がいいのかがわかりません。 このような場合、インデックスを張る方法として、どの方法が一番よいのでしょうか？ 1.検索に使用される10つのフィールドに１つずつ張ればよい 2.検索に使用される10つのフィールドとソートに使用される3つのフィールドに１つずつ張ればよい 3.where句の組み合わせを全て考えて複合インデックスを張る必要がある。 4.その他 また、80フィールドのテーブルを適当に4つくらいに分けて、検索時に結合すれば早くなったりするものでしょうか？

  • SQL文で質問です

    SQL文で質問です。 テーブルA X NUMBER型 Y DATE型（年月日と時間） テーブルB Z NUMBER型 W DATE型（年月日） A,BをAを主テーブルとして外部結合し、同じ年月日でBには無いデータ を取得したいのですが、 SELECT A.X FROM A,B WHERE A.X = B.Z(+) AND A.Y = B.W(+) AND B.Z IS NULL; というSQL文を実行すると、テーブルAには時間まで登録されているので 実際には取得したいデータが存在しても 「レコードが選択されませんでした」 になってしまいます。このため、 SELECT A.X FROM A,B WHERE A.X = B.Z(+) AND TO_DATE(A.Y,'YYYY/MM/DD') = TO_DATE(B.W,'YYYY/MM/DD')(+) AND B.Z IS NULL; としてみたところ、今度は３行目で 「ORA-00936: 式がありません」 というエラーになってしまいます。 外部結合にしたことがエラーの原因のようなのですが、DATE型の項目を キーにして外部結合にするにはどうすれば良いのでしょうか？

  • SQLiteで最も古いレコードのみの削除

    AndoroidでDBを使うのですが、考える動作のSQL文が作成できません。 テーブルTESTは以下のカラムを持ちます 　・ID - primary key not null 　・VALUE - not null テーブルTESTは最大で10件のレコードを保持します、11件目のレコードが発生したら 最も古い１件目のレコードを削除してから、１１件目のデータを新しい１０件目のデータとして テーブルに保存します。 そのために「最も古いレコード１件のみを削除する」というSQLを作成したいのですが、 考えたSQL文が正しくないと怒られてしまいます。 delete from TEST as A, (select * from TEST LIMIT 1)as B where A.ID=B.ID; この動作を１つのSQL文で行うのは不可能なのでしょうか？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する