Re: How does filter order relate to query optimization?

[Andrew Edson <cheighlund@xxxxxxxxx>]

I have now, over two of our setups, and I'm getting another, er, interesting problem.  Same statement, same data, wildly different times.  One's taking nearly half an hour, the other's ready within a few minutes.  It's a rather large database, so I'm not surprised at a little delay (although shortening that as much as possible is the goal), but still...

 

Statement in question.

select substring(ttrans.tran_dt, 1, 10) as tran_dt, ttrans.dist_id as dist_id, ttrans.cntrct_id as cntrct_id, cntrt.cntrtyp_cd as cntrt_type, cntrt.actual_amt as cntrt_amt, acntrec.mth_reck as mth_reck, persn.frst_nm as fnm, persn.lst_nm as lnm from ttrans, cntrt, acntrec, persn, custm, addru where ttrans.tran_dt >= '2007-01-01' and ttrans.tran_dt < '2007-02-01' and ttrans.cntrct_id = cntrt.cntrct_id and cntrt.cntrct_seq = addru.cntrct_seq and addru.aunit_seq = acntrec.aunit_seq and cntrt.cntrtyp_cd = 260 and cntrt.clnt_seq = custm.clnt_seq and custm.person_seq = persn.person_seq and acntrec.cd_inst = 49 and acntrec.months = 49 and cntrt.dow_flg1 = 'NO' order by ttrans.dist_id asc, cntrt.cntrct_id asc, cntrt.cntrct_id asc, cntrt.cntrct_id asc, cntrt.cntrct_id asc;

Results: Slow system

                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=169629.77..169629.78 rows=1 width=91) (actual time=1262832.907..1262833.259 rows=120 loops=1)
   Sort Key: ttrans.dist_id, cntrt.cntrct_id
   ->  Nested Loop  (cost=0.00..169629.76 rows=1 width=91) (actual time=18755.330..1262808.593 rows=120 loops=1)
         Join Filter: (("inner".cntrct_id)::bpchar = "outer".cntrct_id)
         ->  Nested Loop  (cost=0.00..18902.45 rows=1 width=68) (actual time=369.208..20016.454 rows=65 loops=1)
               ->  Nested Loop  (cost=0.00..18897.73 rows=1 width=48) (actual time=347.437..15905.930 rows=65 loops=1)
                     ->  Nested Loop  (cost=0.00..18892.32 rows=1 width=48) (actual time=338.897..12678.319 rows=65 loops=1)
                           ->  Nested Loop  (cost=0.00..18875.15 rows=3 width=43) (actual time=317.317..8104.591 rows=68 loops=1)
                                 ->  Seq Scan on cntrt  (cost=0.00..18857.61 rows=3 width=43) (actual time=317.181..4249.752 rows=68 loops=1)
                                       Filter: (((cntrtyp_cd)::text = '260'::text) AND (dow_flg1 = 'NO'::bpchar))
                                 ->  Index Scan using fk_cntrct on addru  (cost=0.00..5.83 rows=1 width=8) (actual time=56.661..56.666 rows=1 loops=68)
                                       Index Cond: ("outer".cntrct_seq = addru.cntrct_seq)
                           ->  Index Scan using fk_aunit on acntrec  (cost=0.00..5.71 rows=1 width=13) (actual time=66.415..67.243 rows=1 loops=68)
                                 Index Cond: ("outer".aunit_seq = acntrec.aunit_seq)
                                 Filter: ((cd_inst = 49) AND ((months)::text = '49'::text))
                     ->  Index Scan using "pkeyCUSTM" on custm  (cost=0.00..5.39 rows=1 width=8) (actual time=49.633..49.638 rows=1 loops=65)
                           Index Cond: ("outer".clnt_seq = custm.clnt_seq)
               ->  Index Scan using "pkeyPERSN" on persn  (cost=0.00..4.70 rows=1 width=28) (actual time=63.212..63.220 rows=1 loops=65)
                     Index Cond: ("outer".person_seq = persn.person_seq)
         ->  Seq Scan on ttrans  (cost=0.00..149327.10 rows=112017 width=23) (actual time=163.610..18845.905 rows=86415 loops=65)
               Filter: ((tran_dt >= '2007-01-01 00:00:00-06'::timestamp with time zone) AND (tran_dt < '2007-02-01 00:00:00-06'::timestamp with time zone))
 Total runtime: 1262856.689 ms
(22 rows)

Results: 'Normal' system
                                                                               QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=177304.02..177304.03 rows=1 width=125) (actual time=271165.198..271165.432 rows=120 loops=1)
   Sort Key: ttrans.dist_id, cntrt.cntrct_id
   ->  Nested Loop  (cost=0.00..177304.01 rows=1 width=125) (actual time=21612.109..270938.877 rows=120 loops=1)
         ->  Nested Loop  (cost=0.00..177299.40 rows=1 width=106) (actual time=21538.207..267696.943 rows=120 loops=1)
               ->  Nested Loop  (cost=0.00..177294.64 rows=1 width=106) (actual time=21271.967..263316.202 rows=120 loops=1)
                     Join Filter: (("inner".cntrct_id)::bpchar = "outer".cntrct_id)
                     ->  Nested Loop  (cost=0.00..19221.50 rows=1 width=48) (actual time=2057.840..25089.891 rows=65 loops=1)
                           ->  Nested Loop  (cost=0.00..19210.31 rows=2 width=43) (actual time=1884.550..17108.249 rows=68 loops=1)
                                 ->  Seq Scan on cntrt  (cost=0.00..19199.68 rows=2 width=43) (actual time=1590.328..8572.132 rows=68 loops=1)
                                       Filter: (((cntrtyp_cd)::text = '260'::text) AND (dow_flg1 = 'NO'::bpchar))
                                 ->  Index Scan using fk_cntrct on addru  (cost=0.00..5.30 rows=1 width=8) (actual time=125.508..125.513 rows=1 loops=68)
                                       Index Cond: ("outer".cntrct_seq = addru.cntrct_seq)
                           ->  Index Scan using fk_aunit on acntrec  (cost=0.00..5.59 rows=1 width=13) (actual time=117.329..117.340 rows=1 loops=68)
                                 Index Cond: ("outer".aunit_seq = acntrec.aunit_seq)
                                 Filter: ((cd_inst = 49) AND ((months)::text = '49'::text))
                     ->  Seq Scan on ttrans  (cost=0.00..157710.93 rows=28976 width=58) (actual time=39.742..3530.494 rows=86415 loops=65)
                           Filter: ((tran_dt >= '2007-01-01 00:00:00-06'::timestamp with time zone) AND (tran_dt < '2007-02-01 00:00:00-06'::timestamp with time zone))
               ->  Index Scan using "pkeyCUSTM" on custm  (cost=0.00..4.75 rows=1 width=8) (actual time=36.492..36.494 rows=1 loops=120)
                     Index Cond: ("outer".clnt_seq = custm.clnt_seq)
         ->  Index Scan using "pkeyPERSN" on persn  (cost=0.00..4.59 rows=1 width=27) (actual time=26.973..26.981 rows=1 loops=120)
               Index Cond: ("outer".person_seq = persn.person_seq)
 Total runtime: 271175.640 ms
(22 rows)

 

Anybody ideas what might be causing the problems with the slowdown?  The slow database is fed by slony logshipping from the 'normal' one, and both are (at least theoretically) getting vacuumed every night.  What else might be causing this kind of slowdown problem?

Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Andrew Edson writes:
> I'm working on a php project that's supposed to draw information from the DB for display, and I've been requested to speed up the display as much as possible. I'm drawing data from four tables, with an additional two that I have to 'bounce' through to match the keys together. Also, I've got five direct filtering requirements, four of the 'value = X' type and a date range.

> My question is this: Would shuffling the placement of the filtering
> requirements (t1.some_key = t2.some_key and t1.some_other_value = X,
> etc.) make a difference in processing speed for the response time?

No; certainly it will make no difference how you shuffle clauses that
involve different sets of tables. If you've got clauses that wind up in
the same "Filter:" condition in the generated plan, and some of them
involve expensive functions, it might be useful to shuffle the
expensive-to-evaluate ones to the end. But in most cases that's just
micro-optimization. Usually what you want to think about for something
like this is plan optimization, ie, what order are the tables joined in
and with what join methods. Have you looked at EXPLAIN ANALYZE results
for the query?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

---

Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.