performance - PostgreSQL 查询未执行或花费太多时间

Question

我在执行 this 时在 Windows 上使用 PostgreSQL 9.2|查询:

SELECT
    gtab16.VrId,
    coalesce((
        select (gtab09.TdAmt+gtab09.CdAmt)
        from gtab09
        Where gtab09.JrmId = gtab16.JrmId
        limit 1
        ), 0::money
    ) as DisAmount,
    gtab02.VrName,
    gtab02.Vrlongname,
    gtab16.AcYrId,
    CASE WHEN gtab16.vrid = 28 THEN gtab16.RefNo ELSE gtab16.VrNo END AS VrNo,
    gtab16.RefNo,
    CASE
        WHEN gtab16.VrId = 10 THEN cast((
            Select coalesce(PBillDate,null)
            from gtab09
            Where gtab09.JrmId = gtab16.JrmId
        ) as bpchar)
        ELSE ''
    END As BillDate ,
    Cast(gtab16.VrDate As timestamp) As VrDate,
    gtab16.AgeDate,
    (
        SELECT coalesce(Sum(gtab18.ageamt),0::money)
        FROM
            (gtab16 AS A INNER JOIN gtab17 AS B ON A.jrmId = B.JrmId)
            INNER JOIN
            gtab18 ON B.JrDetId = gtab18.crjrdetid
        WHERE gtab18.drjrdetid = Gtab17.JrDetId AND A.AgeDate <= '2014-07-09'
    ) AS AgedAmt,
    case when gtab17.dr > 0::money then gtab17.Dr else gtab17.Cr end  AS VrAmt,
    gtab17.AcId, gtab12.AcName,
    gtab12.AcShortName,
    gtab12.PhoneOff,
    case when gtab17.cr > 0::money then 1 else 0 end AS Receipt,
    gtab47.AreaName,
    gtab16.JrMId,
    gtab17.JrDetId,
    date_part('day','2014-07-09' - Gtab16.agedate) as DayCount,
    (
        SELECT coalesce(sum(chqAmt),0::money)
        From gtab19
        Where PartyAcId = gtab17.acid and vrid = 19 And Pdc =1
    ) as PDCCheq,
    30 As Span1,
    60 As Span2,
    90 As Span3
FROM
    (
        gtab16
        INNER JOIN
        (
            gtab17
            INNER JOIN
            gtab12 ON gtab17.AcId = gtab12.acid
        ) ON gtab16.jrmId = gtab17.JrmId
    )
    INNER JOIN gtab02 ON gtab16.VrId = gtab02.vrId
    INNER JOIN gtab47 ON gtab12.AreaId = gtab47.AreaId
WHERE
    gtab16.BranchID = 1 And
    gtab17.Dr > 0::money AND
    case when gtab16.AcYrid = 2 then 1 else gtab16.VrId  end <> 6 And
    date_part('day','2014-07-09' - Gtab16.agedate)  >= 0 AND
    (gtab12.AcGrCode = '204' or gtab12.AcGrCode = '103') And
    gtab47.AreaId IN (7) AND
    date_part('day',  '2014-07-09' - gtab16.AgeDate) >= 0 And
    (gtab17.Dr - (
        SELECT coalesce(Sum(gtab18.ageamt),0::money)
        From
            (
                gtab16 AS A
                INNER JOIN gtab17 AS B ON A.jrmId = B.JrmId
            )
            INNER JOIN gtab18 ON B.JrDetId = gtab18.crjrdetid
        Where
            gtab18.drjrdetid = Gtab17.JrDetId AND
            A.AgeDate <= '2014-07-09') > 0::money
    ) AND
    gtab16.VrDate Between '2014-07-01' And '2014-07-09'

需要很长时间，here是explain analyze:

"Nested Loop  (cost=0.00..98913858.59 rows=9 width=363) (actual time=302403.378..302628.382 rows=71 loops=1)"
"  ->  Seq Scan on gtab47  (cost=0.00..1.30 rows=1 width=122) (actual time=0.006..0.010 rows=1 loops=1)"
"        Filter: (areaid = 7)"
"        Rows Removed by Filter: 23"
"  ->  Nested Loop  (cost=0.00..98908508.69 rows=9 width=249) (actual time=302400.148..302405.795 rows=71 loops=1)"
"        Join Filter: (gtab16.vrid = gtab02.vrid)"
"        Rows Removed by Join Filter: 3834"
"        ->  Seq Scan on gtab02  (cost=0.00..1.55 rows=55 width=150) (actual time=0.004..0.052 rows=55 loops=1)"
"        ->  Materialize  (cost=0.00..98908499.74 rows=9 width=103) (actual time=5380.762..5498.218 rows=71 loops=55)"
"              ->  Nested Loop  (cost=0.00..98908499.70 rows=9 width=103) (actual time=295941.855..302398.524 rows=71 loops=1)"
"                    Join Filter: (gtab17.jrmid = gtab16.jrmid)"
"                    Rows Removed by Join Filter: 1886191"
"                    ->  Nested Loop  (cost=0.00..98897543.98 rows=2015 width=69) (actual time=7.437..299102.826 rows=2037 loops=1)"
"                          Join Filter: (gtab17.acid = gtab12.acid)"
"                          Rows Removed by Join Filter: 12893055"
"                          ->  Seq Scan on gtab17  (cost=0.00..98819605.03 rows=29138 width=28) (actual time=2.974..276230.715 rows=68228 loops=1)"
"                                Filter: ((dr > (0)::money) AND ((dr - (SubPlan 5)) > (0)::money))"
"                                Rows Removed by Filter: 111761"
"                                SubPlan 5"
"                                  ->  Aggregate  (cost=548.98..549.00 rows=1 width=8) (actual time=3.131..3.132 rows=1 loops=88001)"
"                                        ->  Nested Loop  (cost=0.71..548.98 rows=1 width=8) (actual time=2.707..3.126 rows=0 loops=88001)"
"                                              ->  Nested Loop  (cost=0.42..548.64 rows=1 width=12) (actual time=2.701..3.119 rows=0 loops=88001)"
"                                                    ->  Seq Scan on gtab18 gtab18_1  (cost=0.00..540.19 rows=1 width=12) (actual time=2.693..3.109 rows=0 loops=88001)"
"                                                          Filter: (drjrdetid = gtab17.jrdetid)"
"                                                          Rows Removed by Filter: 28575"
"                                                    ->  Index Scan using gtab17_pkey on gtab17 b_1  (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=28574)"
"                                                          Index Cond: (jrdetid = gtab18_1.crjrdetid)"
"                                              ->  Index Scan using gtab16_pkey on gtab16 a_1  (cost=0.29..0.33 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=28574)"
"                                                    Index Cond: (jrmid = b_1.jrmid)"
"                                                    Filter: (agedate <= '2014-07-09 00:00:00'::timestamp without time zone)"
"                          ->  Materialize  (cost=0.00..140.94 rows=178 width=45) (actual time=0.001..0.160 rows=189 loops=68228)"
"                                ->  Seq Scan on gtab12  (cost=0.00..140.05 rows=178 width=45) (actual time=0.057..0.927 rows=189 loops=1)"
"                                      Filter: ((areaid = 7) AND (((acgrcode)::text = '204'::text) OR ((acgrcode)::text = '103'::text)))"
"                                      Rows Removed by Filter: 2385"
"                    ->  Materialize  (cost=0.00..3037.42 rows=262 width=38) (actual time=0.006..0.788 rows=926 loops=2037)"
"                          ->  Seq Scan on gtab16  (cost=0.00..3036.11 rows=262 width=38) (actual time=10.342..13.037 rows=926 loops=1)"
"                                Filter: ((vrdate >= '2014-07-01 00:00:00'::timestamp without time zone) AND (vrdate <= '2014-07-09 00:00:00'::timestamp without time zone) AND (branchid = 1) AND (CASE WHEN (acyrid = 2) THEN 1 ELSE vrid END <> 6) AND (date_p (...)"
"                                Rows Removed by Filter: 58837"
"  SubPlan 1"
"    ->  Limit  (cost=0.29..8.31 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=71)"
"          ->  Index Scan using gtab09_jrmid_idx on gtab09  (cost=0.29..8.31 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=71)"
"                Index Cond: (jrmid = gtab16.jrmid)"
"  SubPlan 2"
"    ->  Index Scan using gtab09_jrmid_idx on gtab09 gtab09_1  (cost=0.29..8.31 rows=1 width=8) (never executed)"
"          Index Cond: (jrmid = gtab16.jrmid)"
"  SubPlan 3"
"    ->  Aggregate  (cost=548.98..549.00 rows=1 width=8) (actual time=2.975..2.975 rows=1 loops=71)"
"          ->  Nested Loop  (cost=0.71..548.98 rows=1 width=8) (actual time=2.968..2.970 rows=0 loops=71)"
"                ->  Nested Loop  (cost=0.42..548.64 rows=1 width=12) (actual time=2.965..2.966 rows=0 loops=71)"
"                      ->  Seq Scan on gtab18  (cost=0.00..540.19 rows=1 width=12) (actual time=2.959..2.960 rows=0 loops=71)"
"                            Filter: (drjrdetid = gtab17.jrdetid)"
"                            Rows Removed by Filter: 28575"
"                      ->  Index Scan using gtab17_pkey on gtab17 b  (cost=0.42..8.44 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)"
"                            Index Cond: (jrdetid = gtab18.crjrdetid)"
"                ->  Index Scan using gtab16_pkey on gtab16 a  (cost=0.29..0.33 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=1)"
"                      Index Cond: (jrmid = b.jrmid)"
"                      Filter: (agedate <= '2014-07-09 00:00:00'::timestamp without time zone)"
"  SubPlan 4"
"    ->  Aggregate  (cost=28.63..28.64 rows=1 width=8) (actual time=0.130..0.131 rows=1 loops=71)"
"          ->  Seq Scan on gtab19  (cost=0.00..28.62 rows=1 width=8) (actual time=0.124..0.124 rows=0 loops=71)"
"                Filter: ((partyacid = gtab17.acid) AND (vrid = 19) AND (pdc = 1))"
"                Rows Removed by Filter: 607"
"Total runtime: 302628.704 ms"

• PostgreSQL 服务器端 config

max_connections 10000
max_stack_depth 2MB
shared_buffers 1GB
temp_buffers 1GB

我是新手，所以非常感谢任何建议或意见

Answer 1

关于 http://explain.depesz.com/ ，您将看到您的查询在哪里变慢。

但在进行查询优化之前，请阅读有关它的教程以了解什么是 seqscan/loop/cast 等...请尝试重写您的查询以使其更简单!我想你可以。

阅读@wildplasser 所说的，因为 Select coalesce(PBillDate,null) from 是由于对 coalesce 的不全面使用...Select PBillDate from 是相同的，所以不要'不需要治疗。

但为了帮助您，当我遇到这种情况时，我尝试删除所有 select 子句以仅保留一个并删除我的 select 子句不需要的连接并播放查询。尝试使用正确的连接一个一个地重新添加 select 子句，如果您发现查询时间增加，请进行解释，转到 depesz 网站阅读解释并尝试检测查询缓慢的位置并添加索引或重写它.

读取大查询并尝试对其进行优化非常困难，因此逐步添加 select 子句，您将找到解决方案。

并检查您的统计数据是否正确，如果不正确，您的解释将不正确。你可以做真空来刷新你的统计数据。并尝试使用 explain analyze 而不是 explain 因为 explain analyze 进行了真正的查询并返回许多其他信息以查看每个部分的成本。

阅读关于解释分析的教程，因为你有很多非常有用的选项；)

最好的问候