sql - Postgresql ORDER BY of indexed columns is slow -

sql - Postgresql ORDER BY of indexed columns is slow -

i've big query, in end i've order criterias. main table of query has 1 500 000 rows, , i've lot of bring together statements. when run query takes more 9 minutes, when run explain select, clear "order by" clause, takes lot of time. columns after order statement indexed btree, , bigint. can suggest can create execution time less? below part of explain analyze select... (it takes more 9 minutes complete)

the query:

explain analyse select aa2.aid cid ,aa2.id ,aa2.ty ,coalesce(sign.bb,'') tyy ,aa2.rd ,aa2.dt ,aa2.appliedon ,aa2.approvedon ,aa2.pprec ,aa2.ppret ,aa2.cdate ,pm.pmd,tblapplic.dob ,coalesce(tblapplic.firstname ,'') fname ,coalesce(tblapplic.middlename ,'') mname ,coalesce(tblapplic.surname ,'') sname ,coalesce(upper(dcou.descr),'') dcouu ,coalesce(aa2.visaid,0) visaid ,coalesce(tblcountry.descr,'') visaname ,(select chr(9) || array_to_string(array(select descr tblcountry dcountry=travdets.dcountry order descr),chr(9))) visanamecombo ,coalesce(aa2.stype,0) stypeid ,coalesce(stype.descr,'') stype ,coalesce(aa2.sts,0) appstatusid ,coalesce(sta.descr,'') appstatus ,lofficesuperuser.descr lofficesuperuser,coalesce(aa2.loffice,0) lofficeid ,coalesce(lofficesuperuser.descr,coalesce(loffice.descr,'')) loffice ,coalesce(pm.id,0) pmid ,(select chr(9) || array_to_string(array(select descr stype typee=aa2.ty , visaa=coalesce(aa2.visaid,0) order descr),chr(9))) stypecombo ,(select chr(9) || array_to_string(array(select descr sta typee=aa2.ty order descr),chr(9))) appstatuscombo ,coalesce(aa2.colltype,0) colltypeid ,coalesce(tblcolltype.descr,'') colltype,aa2.embcolldt embcolldt ,aa2.clcolldt clbcolldt,travdets.leavingdt leaving,coalesce(aa2.pptrnum,'') pptracknumber ,coalesce(aa2.rt,0) regtypeid,coalesce(tbllist1.descr,'') regtype ,coalesce(aa2.reff,0) referralid ,coalesce(tblreferral.descr,'') referral ,coalesce(aa2.scaller,0) callerid ,coalesce(namess.descr,'') caller,coalesce(aa2.travdets,0) travdetsid ,coalesce(curact.luser,-1) actionluser ,aa2.iss aa2 left bring together tblapplic on aa2.aid=tblapplic.idnumber left bring together travdets on aa2.travdets=travdets.idnumber left bring together pm on aa2.id=pm.aa2id , pm.pagee=aa2.ty left bring together sign on aa2.ty = sign.aa left bring together tblnationality dcou on dcou.idnumber=travdets.dcountry left bring together tblcountry on aa2.visaid=tblcountry.idnumber left bring together aa2pre on aa2.id=aa2pre.aa2id left bring together tbluser on aa2pre.addusr=tbluser.idnumber left bring together loffice on tbluser.office=loffice.idnumber left bring together loffice lofficesuperuser on aa2.loffice=lofficesuperuser.idnumber left outer bring together stype on aa2.stype=stype.id left outer bring together sta on aa2.sts=sta.idnumber , sta.typee=aa2.ty left bring together tblcolltype on aa2.colltype=tblcolltype.idnumber left bring together tbllist1 on aa2.rt=tbllist1.idnumber left bring together tblreferral on aa2.reff=tblreferral.idnumber left bring together namess on aa2.scaller=namess.idnumber left bring together aa2_curr_act curact on aa2.id=curact.aa2id aa2.op_status=0 , aa2.ty>0 order aa2.aid desc, aa2.ty desc limit 1000

the plan:

limit (cost=2502213.29..2502215.79 rows=1000 width=555) (actual time=569132.700..569133.021 rows=1000 loops=1) -> sort (cost=2502213.29..2502284.78 rows=28593 width=555) (actual time=569132.699..569132.870 rows=1000 loops=1) sort key: aa2.aid, aa2.ty sort method: top-n heapsort memory: 3539kb -> hash left bring together (cost=424537.96..2500645.57 rows=28593 width=555) (actual time=3817.372..565313.067 rows=1175709 loops=1) hash cond: (aa2.id = curact.aa2id) -> hash left bring together (cost=424498.48..469748.41 rows=28593 width=547) (actual time=3816.435..28001.006 rows=1175709 loops=1) hash cond: (aa2.scaller = namess.idnumber) -> hash left bring together (cost=424492.25..469350.45 rows=28593 width=532) (actual time=3816.326..27006.364 rows=1175709 loops=1) hash cond: (aa2.reff = tblreferral.idnumber) -> hash left bring together (cost=424452.85..468924.32 rows=28593 width=506) (actual time=3815.846..25976.031 rows=1175709 loops=1) hash cond: (aa2.rt = tbllist1.idnumber) -> nested loop left bring together (cost=424451.44..468537.08 rows=28593 width=487) (actual time=3815.819..25098.987 rows=1175709 loops=1) bring together filter: (aa2.colltype = tblcolltype.idnumber)" rows removed bring together filter: 8229962 -> hash left bring together (cost=424451.44..465533.73 rows=28593 width=451) (actual time=3815.793..21647.918 rows=1175709 loops=1) hash cond: ((aa2.sts = public.sta.idnumber) , (aa2.ty = public.sta.typee)) -> hash left bring together (cost=424443.94..464745.17 rows=28593 width=424) (actual time=3815.677..20748.314 rows=1175709 loops=1) hash cond: (aa2.stype = public.stype.id)" -> hash left bring together (cost=424367.49..464239.83 rows=28593 width=383) (actual time=3814.669..19892.991 rows=1175709 loops=1) hash cond: (aa2.loffice = lofficesuperuser.idnumber) -> hash left bring together (cost=424366.18..464131.28 rows=28593 width=374) (actual time=3814.651..19300.840 rows=1175709 loops=1) hash cond: (tbluser.office = loffice.idnumber) -> hash left bring together (cost=424364.86..464002.72 rows=28593 width=373) (actual time=3814.628..18721.215 rows=1175709 loops=1) hash cond: (aa2pre.addusr = tbluser.idnumber) -> hash left bring together (cost=424329.53..463574.24 rows=28593 width=373) (actual time=3813.991..18128.257 rows=1175709 loops=1) hash cond: (aa2.id = aa2pre.aa2id) -> hash left bring together (cost=424309.57..463447.04 rows=28593 width=365) (actual time=3813.404..17427.883 rows=1175708 loops=1) hash cond: (aa2.visaid = public.tblcountry.idnumber) -> hash left bring together (cost=424286.05..463120.22 rows=28593 width=342) (actual time=3813.207..16687.672 rows=1175708 loops=1) hash cond: (travdets.dcountry = dcou.idnumber) -> hash left bring together (cost=424277.35..462718.39 rows=28593 width=330) (actual time=3813.056..15885.221 rows=1175708 loops=1) hash cond: (aa2.ty = sign.aa) -> hash left bring together (cost=424275.90..462337.40 rows=28593 width=319) (actual time=3813.019..15036.693 rows=1175708 loops=1) hash cond: (aa2.travdets = travdets.idnumber) -> hash left bring together (cost=408250.71..445773.33 rows=28593 width=307) (actual time=3559.650..12275.923 rows=1175708 loops=1)" hash cond: (aa2.aid = tblapplic.idnumber) -> hash right bring together (cost=301618.16..338733.36 rows=28593 width=287) (actual time=2854.476..9334.815 rows=1175708 loops=1) hash cond: ((pm.aa2id = aa2.id) , (pm.pagee = aa2.ty)) -> seq scan on pm (cost=0.00..23718.76 rows=595176 width=22) (actual time=0.148..270.163 rows=583001 loops=1) -> hash (cost=301189.26..301189.26 rows=28593 width=275) (actual time=2854.215..2854.215 rows=1167712 loops=1) buckets: 4096 batches: 4 (originally 1) memory usage: 65537kb -> seq scan on aa2 (cost=0.00..301189.26 rows=28593 width=275) (actual time=0.010..1806.989 rows=1167712 loops=1) filter: ((ty > 0) , (op_status = 0))" rows removed filter: 98527 -> hash (cost=98044.47..98044.47 rows=687047 width=28) (actual time=705.012..705.012 rows=447141 loops=1) buckets: 131072 batches: 1 memory usage: 28576kb" -> seq scan on tblapplic (cost=0.00..98044.47 rows=687047 width=28) (actual time=0.012..366.413 rows=447141 loops=1) -> hash (cost=10560.64..10560.64 rows=437164 width=20) (actual time=253.078..253.078 rows=419170 loops=1) buckets: 65536 batches: 1 memory usage: 22917kb" -> seq scan on travdets (cost=0.00..10560.64 rows=437164 width=20) (actual time=0.006..122.472 rows=419170 loops=1) -> hash (cost=1.20..1.20 rows=20 width=19) (actual time=0.014..0.014 rows=20 loops=1)" buckets: 1024 batches: 1 memory usage: 2kb" -> seq scan on sign (cost=0.00..1.20 rows=20 width=19) (actual time=0.004..0.009 rows=20 loops=1) -> hash (cost=5.53..5.53 rows=253 width=20) (actual time=0.143..0.143 rows=253 loops=1) buckets: 1024 batches: 1 memory usage: 14kb -> seq scan on tblnationality dcou (cost=0.00..5.53 rows=253 width=20) (actual time=0.007..0.064 rows=253 loops=1) -> hash (cost=19.90..19.90 rows=290 width=31) (actual time=0.189..0.189 rows=294 loops=1) buckets: 1024 batches: 1 memory usage: 20kb" -> seq scan on tblcountry (cost=0.00..19.90 rows=290 width=31) (actual time=0.004..0.094 rows=294 loops=1) -> hash (cost=17.76..17.76 rows=176 width=16) (actual time=0.576..0.576 rows=431 loops=1) buckets: 1024 batches: 1 memory usage: 21kb -> seq scan on aa2pre (cost=0.00..17.76 rows=176 width=16) (actual time=0.030..0.438 rows=494 loops=1) -> hash (cost=28.48..28.48 rows=548 width=16) (actual time=0.623..0.623 rows=507 loops=1) buckets: 1024 batches: 1 memory usage: 22kb -> seq scan on tbluser (cost=0.00..28.48 rows=548 width=16) (actual time=0.005..0.526 rows=507 loops=1) -> hash (cost=1.14..1.14 rows=14 width=17) (actual time=0.010..0.010 rows=13 loops=1)" buckets: 1024 batches: 1 memory usage: 1kb" -> seq scan on loffice (cost=0.00..1.14 rows=14 width=17) (actual time=0.003..0.005 rows=13 loops=1)" -> hash (cost=1.14..1.14 rows=14 width=17) (actual time=0.008..0.008 rows=13 loops=1) buckets: 1024 batches: 1 memory usage: 1kb -> seq scan on loffice lofficesuperuser (cost=0.00..1.14 rows=14 width=17) (actual time=0.002..0.005 rows=13 loops=1) -> hash (cost=55.09..55.09 rows=1709 width=49) (actual time=0.998..0.998 rows=1717 loops=1) buckets: 1024 batches: 1 memory usage: 144kb -> seq scan on stype (cost=0.00..55.09 rows=1709 width=49) (actual time=0.005..0.431 rows=1717 loops=1) -> hash (cost=5.40..5.40 rows=140 width=37) (actual time=0.100..0.100 rows=145 loops=1) buckets: 1024 batches: 1 memory usage: 11kb -> seq scan on sta (cost=0.00..5.40 rows=140 width=37) (actual time=0.004..0.046 rows=145 loops=1) -> materialize (cost=0.00..1.10 rows=7 width=44) (actual time=0.000..0.001 rows=7 loops=1175709) -> seq scan on tblcolltype (cost=0.00..1.07 rows=7 width=44) (actual time=0.013..0.014 rows=7 loops=1) -> hash (cost=1.18..1.18 rows=18 width=27) (actual time=0.012..0.012 rows=18 loops=1) buckets: 1024 batches: 1 memory usage: 2kb" -> seq scan on tbllist1 (cost=0.00..1.18 rows=18 width=27) (actual time=0.004..0.007 rows=18 loops=1) -> hash (cost=29.18..29.18 rows=818 width=34) (actual time=0.469..0.469 rows=827 loops=1)" buckets: 1024 batches: 1 memory usage: 57kb" -> seq scan on tblreferral (cost=0.00..29.18 rows=818 width=34) (actual time=0.007..0.181 rows=827 loops=1) -> hash (cost=3.88..3.88 rows=188 width=23) (actual time=0.099..0.099 rows=191 loops=1) buckets: 1024 batches: 1 memory usage: 11kb -> seq scan on namess (cost=0.00..3.88 rows=188 width=23) (actual time=0.007..0.042 rows=191 loops=1) -> hash (cost=23.10..23.10 rows=1310 width=16) (actual time=0.000..0.000 rows=0 loops=1) buckets: 1024 batches: 1 memory usage: 0kb -> seq scan on aa2_curr_act curact (cost=0.00..23.10 rows=1310 width=16) (actual time=0.000..0.000 rows=0 loops=1) subplan 2 -> result (cost=20.66..20.67 rows=1 width=0) (actual time=0.156..0.156 rows=1 loops=1175709) initplan 1 (returns $1) -> sort (cost=20.65..20.66 rows=3 width=23) (actual time=0.135..0.137 rows=25 loops=1175709) sort key: public.tblcountry.descr sort method: quicksort memory: 28kb -> seq scan on tblcountry (cost=0.00..20.62 rows=3 width=23) (actual time=0.017..0.063 rows=25 loops=1175709) filter: (dcountry = travdets.dcountry) rows removed filter: 269 subplan 4 -> result (cost=44.29..44.31 rows=1 width=0) (actual time=0.237..0.237 rows=1 loops=1175709) initplan 3 (returns $4) -> sort (cost=44.29..44.29 rows=1 width=41) (actual time=0.224..0.225 rows=12 loops=1175709) sort key: public.stype.descr sort method: quicksort memory: 25kb -> bitmap heap scan on stype (cost=4.93..44.28 rows=1 width=41) (actual time=0.108..0.197 rows=12 loops=1175709) recheck cond: (typee = aa2.ty) filter: (visaa = coalesce(aa2.visaid, 0::bigint)) rows removed filter: 629 -> bitmap index scan on stypeindtypee (cost=0.00..4.93 rows=90 width=0) (actual time=0.073..0.073 rows=745 loops=1175709) index cond: (typee = aa2.ty) subplan 6 -> result (cost=5.87..5.88 rows=1 width=0) (actual time=0.056..0.056 rows=1 loops=1175709) initplan 5 (returns $6) -> sort (cost=5.85..5.87 rows=7 width=27) (actual time=0.044..0.045 rows=13 loops=1175709) sort key: public.sta.descr sort method: quicksort memory: 25kb -> seq scan on sta (cost=0.00..5.75 rows=7 width=27) (actual time=0.010..0.025 rows=13 loops=1175709) filter: (typee = aa2.ty) rows removed filter: 132 total runtime: 569133.611 ms

the reason behind query not using of table's indexes.

for query requires scanning big fraction of table, explicit sort faster using index because requires less disk i/o due next sequential access pattern. indexes more useful when few rows need fetched. 1

if ordery by can utilize index, see this, in explain:

index scan using test_idx on test_tbl (cost=0.15..24.28 rows=285 width=27)

f.ex. in (smaller) table index used, if used limit 210, entire table scan used, if supplied limit 211 (your table can differ however, query planner can take row number & width business relationship f.ex., while deciding utilize index, or not).

sql postgresql