Postgresql ORDER BY of indexed columns is slow

Question

I've a very big query, that in the end I've to ORDER BY some criterias. The main table of the query has about 1 500 000 rows, and I've a lot of JOIN statements. When I run the query it takes more than 9 minutes, when a run the EXPLAIN SELECT, it was clear it is the "ORDER BY" Clause, that takes a lot of time. The columns that are after the ORDER BY statement are indexed with btree, and are BIGINT. Can anyone suggest what can I do to make the execution time less? Below is a part of the EXPLAIN ANALYZE SELECT... (it takes more than 9 minutes to complete)

The Query:

explain ANALYSE
select aa2.aid as cid
,aa2.id ,aa2.ty 
,coalesce(sign.bb,'') as tyy
,aa2.rd ,aa2.dt ,aa2.appliedon 
,aa2.approvedon ,aa2.pprec 
,aa2.ppret ,aa2.cdate ,pm.pmd,tblapplic.dob 
,coalesce(tblapplic.firstname ,'') as fname 
,coalesce(tblapplic.middlename ,'') as mname 
,coalesce(tblapplic.surname ,'') as sname 
,coalesce(Upper(dcou.descr),'') as dcouu 
,coalesce(aa2.visaid,0) as visaid 
,coalesce(tblcountry.descr,'') as visaname 
,(SELECT chr(9) || array_to_string(ARRAY(SELECT descr FROM tblcountry WHERE dcountry=travdets.dcountry ORDER BY descr),chr(9))) as visanamecombo 
,coalesce(aa2.stype,0) as stypeid ,coalesce(stype.descr,'') as stype 
,coalesce(aa2.sts,0) as appStatusid ,coalesce(sta.descr,'') as appStatus 
,lofficesuperuser.descr as lofficesuperuser,coalesce(aa2.loffice,0) as lofficeid 
,coalesce(lofficesuperuser.descr,coalesce(loffice.descr,'')) as loffice 
,coalesce(pm.id,0) as pmid 
,(SELECT chr(9) || array_to_string(ARRAY(SELECT descr FROM stype WHERE typee=aa2.ty and visaa=coalesce(aa2.visaid,0) ORDER BY descr),chr(9))) as sTypeCombo 
,(SELECT chr(9) || array_to_string(ARRAY(SELECT descr FROM sta WHERE typee=aa2.ty ORDER BY descr),chr(9))) as appStatusCombo 
,coalesce(aa2.colltype,0) as colltypeid
,coalesce(tblcolltype.descr,'') as colltype,aa2.embcolldt as embcolldt
,aa2.clcolldt as clbcolldt,travdets.leavingdt as leaving,coalesce(aa2.pptrnum,'') as ppTrackNumber
,coalesce(aa2.rt,0) as regtypeid,coalesce(tbllist1.descr,'') as regtype
,coalesce(aa2.reff,0) as referralid ,coalesce(tblreferral.descr,'') as referral
,coalesce(aa2.scaller,0) as callerid 
,coalesce(namess.descr,'') as caller,coalesce(aa2.travdets,0) as travdetsid
,coalesce(curact.luser,-1) as actionluser
,aa2.iss 
from aa2  
LEFT JOIN tblapplic on aa2.aid=tblapplic.idnumber  
left JOIN travdets on aa2.travdets=travdets.idnumber  
left JOIN pm on aa2.id=pm.aa2id  and pm.pagee=aa2.ty 
LEFT JOIN sign ON aa2.ty = sign.aa  
LEFT JOIN tblnationality dcou ON dcou.idnumber=travdets.dcountry 
left join tblcountry on aa2.visaid=tblcountry.idnumber  
left join aa2pre on aa2.id=aa2pre.aa2id 
left join tbluser on aa2pre.addusr=tbluser.idnumber  
left join loffice on tbluser.office=loffice.idnumber  
left join loffice lofficesuperuser on aa2.loffice=lofficesuperuser.idnumber  
left outer join stype on aa2.stype=stype.id  
left outer join sta on aa2.sts=sta.idnumber and sta.typee=aa2.ty 
left join tblcolltype on aa2.colltype=tblcolltype.idnumber  
left join tbllist1 on aa2.rt=tbllist1.idnumber  
left join tblreferral on aa2.reff=tblreferral.idnumber  
left join namess on aa2.scaller=namess.idnumber  
left join aa2_curr_act curact on aa2.id=curact.aa2id 
where aa2.op_status=0 and aa2.ty>0
ORDER BY 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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (cost=424451.44..468537.08 rows=28593 width=487) (actual time=3815.819..25098.987 rows=1175709 loops=1)
                                  Join Filter: (aa2.colltype = tblcolltype.idnumber)"
                                  Rows Removed by Join Filter: 8229962
                                  ->  Hash Left Join  (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) AND (aa2.ty = public.sta.typee))
                                        ->  Hash Left Join  (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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (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 Join  (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) AND (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) AND (op_status = 0))"
                                                                                                                      Rows Removed by 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 by 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 by 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 by Filter: 132
Total runtime: 569133.611 ms

LATEST EDIT:

Here is a workaround, for those that have no time to solve the problem. Just wrap the big query, and then make a ORDER BY. Something like this:

explain ANALYSE
SELECT * FROM
(select aa2.aid as cid
,aa2.id ,aa2.ty 
,coalesce(sign.bb,'') as tyy
,aa2.rd ,aa2.dt ,aa2.appliedon 
,aa2.approvedon ,aa2.pprec 
,aa2.ppret ,aa2.cdate ,pm.pmd,tblapplic.dob 
,coalesce(tblapplic.firstname ,'') as fname 
,coalesce(tblapplic.middlename ,'') as mname 
,coalesce(tblapplic.surname ,'') as sname 
,coalesce(Upper(dcou.descr),'') as dcouu 
,coalesce(aa2.visaid,0) as visaid 
,coalesce(tblcountry.descr,'') as visaname 
,(SELECT chr(9) || array_to_string(ARRAY(SELECT descr FROM tblcountry WHERE dcountry=travdets.dcountry ORDER BY descr),chr(9))) as visanamecombo 
,coalesce(aa2.stype,0) as stypeid ,coalesce(stype.descr,'') as stype 
,coalesce(aa2.sts,0) as appStatusid ,coalesce(sta.descr,'') as appStatus 
,lofficesuperuser.descr as lofficesuperuser,coalesce(aa2.loffice,0) as lofficeid 
,coalesce(lofficesuperuser.descr,coalesce(loffice.descr,'')) as loffice 
,coalesce(pm.id,0) as pmid 
,(SELECT chr(9) || array_to_string(ARRAY(SELECT descr FROM stype WHERE typee=aa2.ty and visaa=coalesce(aa2.visaid,0) ORDER BY descr),chr(9))) as sTypeCombo 
,(SELECT chr(9) || array_to_string(ARRAY(SELECT descr FROM sta WHERE typee=aa2.ty ORDER BY descr),chr(9))) as appStatusCombo 
,coalesce(aa2.colltype,0) as colltypeid
,coalesce(tblcolltype.descr,'') as colltype,aa2.embcolldt as embcolldt
,aa2.clcolldt as clbcolldt,travdets.leavingdt as leaving,coalesce(aa2.pptrnum,'') as ppTrackNumber
,coalesce(aa2.rt,0) as regtypeid,coalesce(tbllist1.descr,'') as regtype
,coalesce(aa2.reff,0) as referralid ,coalesce(tblreferral.descr,'') as referral
,coalesce(aa2.scaller,0) as callerid 
,coalesce(namess.descr,'') as caller,coalesce(aa2.travdets,0) as travdetsid
,coalesce(curact.luser,-1) as actionluser
,aa2.iss 
from aa2  
LEFT JOIN tblapplic on aa2.aid=tblapplic.idnumber  
left JOIN travdets on aa2.travdets=travdets.idnumber  
left JOIN pm on aa2.id=pm.aa2id  and pm.pagee=aa2.ty 
LEFT JOIN sign ON aa2.ty = sign.aa  
LEFT JOIN tblnationality dcou ON dcou.idnumber=travdets.dcountry 
left join tblcountry on aa2.visaid=tblcountry.idnumber  
left join aa2pre on aa2.id=aa2pre.aa2id 
left join tbluser on aa2pre.addusr=tbluser.idnumber  
left join loffice on tbluser.office=loffice.idnumber  
left join loffice lofficesuperuser on aa2.loffice=lofficesuperuser.idnumber  
left outer join stype on aa2.stype=stype.id  
left outer join sta on aa2.sts=sta.idnumber and sta.typee=aa2.ty 
left join tblcolltype on aa2.colltype=tblcolltype.idnumber  
left join tbllist1 on aa2.rt=tbllist1.idnumber  
left join tblreferral on aa2.reff=tblreferral.idnumber  
left join namess on aa2.scaller=namess.idnumber  
left join aa2_curr_act curact on aa2.id=curact.aa2id 
where aa2.op_status=0 and aa2.ty>0)
ORDER BY aa2.aid DESC, aa2.ty DESC 
LIMIT 1000

The query plan shows that sorting was done in ~0.2 ms. The part of the query before sort - 557395.213 ms. You can try to execute the same query without the sort and check its plan. — Ihor Romanchenko, Jun 19 '14 at 13:49
Limit (cost=44.94..93484.76 rows=1000 width=555) (actual time=1.283..391.665 rows=1000 loops=1) -> Nested Loop Left Join (cost=44.94..2671769.72 rows=28593 width=555) (actual time=1.282..391.253 rows=1000 loops=1) ...... -->execution time: 562 ms; total time: 625 ms — Julian, Jun 19 '14 at 13:50
Thats a very different execution plan. It does not need to read all data for sorting. Post your full query and query plan. — Ihor Romanchenko, Jun 19 '14 at 13:55

score 1 · Answer 1 · answered Jun 19 '14 at 14:09

1

The reason behind this is that your query is not using any of that table's indexes.

For a query that requires scanning a large fraction of the table, an explicit sort is likely to be faster than using an index because it requires less disk I/O due to following a sequential access pattern. Indexes are more useful when only a few rows need be fetched. ¹

If an ORDERY BY can use an index, you would see something like this, in your EXPLAIN:

Index Scan using test_idx on test_tbl (cost=0.15..24.28 rows=285 width=27)

F.ex. in my (smaller) table this index is used, if a used LIMIT 210, but an entire table scan is used, if I supplied LIMIT 211 (your table can differ however, the query planner can take row number & width into account f.ex., while deciding to use an index, or not).

answered Jun 19 '14 at 14:09

pozs

34,608
5
57
63

How can I force to use index scan, to compare the two executions? – Julian Jun 19 '14 at 14:31
You can only do it globally, and **not suitable for ongoing production use**: http://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index Better would be to re-structure your query. – pozs Jun 19 '14 at 14:41
@pozs You can disable seq scans not only globally but on session/transaction level too. – Ihor Romanchenko Jun 19 '14 at 17:07
@IgorRomanchenko yes, I meant an index cannot be forced (i.e. seq scans disabled) in a single table / query only. – pozs Jun 20 '14 at 08:11

wildplasser · Answer 2 · 2014-06-19T19:08:36.263

[ I assume that your data model has Primary Keys, Foreign Keys, and the appropiate indexes for the Foreign Keys ]
for the inner parts of the query, there appears to be a difference between expected and observed number of rows, indicating that statistics are absent or out of date. This could have caused the planner to choose a hashed join, but its size could have caused the hashtables to spill to disk. Try running VACUUM ANALYZE tbl_name; on the affected tables, this will freshen the statistics.
If you have plenty of memory and not too many concurrent sessions you could try setting work_mem a bit higher, this could enable the engine to keep its hash tables in core.
The number of joined tables is rather large, and probably larger than join_collapse_limit (which has a default setting of 8). Try setting it a bit higher, this could enable the optimiser to see beyond its current horizon.
Once you start tuning, try setting effective_cach_size to at least half you physical memory, and random_page_cost to a lower value than the default 4, (try 1.5)

This tuning will favor index-joins above hash-joins, where possible.

[all of the config settings can be set inside the current .sql, by issuing SET parameter_name = value; ] Updating the statistics is permanent, but can't do any harm.

Thank you for your reply. I've ran VACUUM ANALYZE to all tables; the `work_mem` is set to 64MB; the `join_collapse_limit` is set to 20; the `effective_cache_size` is 14GB; the `random_page_cost` is 1.5; Only the `join_collapse_limit` affect to the execution time, reducing it from more than 9 minutes to 1 minute. But still, if I remove the ORDER BY statement, the query execution time is less than a second — Julian, Jun 20 '14 at 08:17
Can't add it because I exceed the max number of symbols allowed :( — Julian, Jun 23 '14 at 07:10
Hi there, I couldn't understand what was the problem, but I ran out of time, and had no more to tackle it. Instead I make a workaround. I'll edit the original post — Julian, Dec 02 '16 at 11:27

score 0 · Accepted Answer · answered May 27 '20 at 10:42

0

Here is a workaround, for those that have no time to solve the problem. Just wrap the big query, and then make a ORDER BY.

answered May 27 '20 at 10:42

Julian

375
1
8
23

Postgresql ORDER BY of indexed columns is slow

3 Answers3