1

I have tables Foo and Buzz like so:

Foos
buzz_id
date

Foo has an index on buzz_id, the foreign key. It also has a index on the date.

Buzzes
name
group

Buzz has an index on name, an index on group, and a multi column unique index that combines name and group. A Buzz has many Foo.

I am making the following query, and it is taking much too long:

SELECT DISTINCT ON (foos.buzz_id) foos.id, foos.date, buzzes.name, buzzes.group FROM foos INNER JOIN buzzes ON buzzes.id = foos.buzz_id 
WHERE (buzzes.group = ANY (ARRAY_OF_GROUPS) 
AND buzzes.name = ANY (ARRAY_OF_NAMES)
AND foos.date <= GIVEN_DATE) ORDER BY foos.buzz_id DESC, foos.date DESC;

I am joining the two tables on the foreign key, and trying to get the highest date of a foo per buzz_id (granted that the buzz's name and group are included in my given arrays and the date condition is satisfied).

I have two environments where I'm running this query, my local machine and a Heroku environment. My local environment has smaller tables as you can see:

Local:
 foos                                        | r          |   4.013e+06 | 639 MB
 foos_pkey                                   | i          | 4.19832e+06 | 198 MB
 index_foos_on_buzz_id                       | i          | 4.19832e+06 | 285 MB
 index_foos_on_date                          | i          | 4.19832e+06 | 330 MB
 buzzes                                      | r          |        2298 | 184 kB
 index_buzzes_on_name_and_group              | i          |        2298 | 120 kB
 index_buzzes_on_group                       | i          |        2298 | 104 kB
 index_buzzes_on_name                        | i          |        2298 | 88 kB

Heroku:
 foos                                        | r          | 4.92772e+07 | 6653 MB 
 foos_pkey                                   | i          | 4.90556e+07 | 3151 MB
 index_foos_on_buzz_id                       | i          | 4.90556e+07 | 2462 MB
 index_foos_on_date                          | i          | 4.90556e+07 | 2421 MB
 buzzes                                      | r          |      328250 | 24 MB
 index_buzzes_on_name_and_group              | i          |      328250 | 10200 kB
 index_buzzes_on_group                       | i          |      328250 | 8624 kB
 index_buzzes_on_name                        | i          |      328250 | 7224 kB

My local tables are missing data so the query is returning fewer rows than the Heroku environment.

I have a large number of items in the ARRAY_OF_NAMES, 500 in this instance, while relatively fewer items are in the ARRAY_OF_GROUPS, let's say 4.

My Heroku environment does not have the amount of RAM to hold all of my data in cache, so I know that at the moment the speed of the query is slower than if it were all cached.

Running the queries with EXPLAIN ANALYZE gives me the following output:

Local:
 Unique  (cost=330087.91..336514.17 rows=1485 width=46) (actual time=3602.511..4131.322 rows=736 loops=1)
   ->  Sort  (cost=330087.91..333301.04 rows=1285252 width=46) (actual time=3602.509..4003.598 rows=1404653 loops=1)
         Sort Key: foos.buzz_id DESC, foos.date DESC
         Sort Method: external merge  Disk: 96096kB
         ->  Hash Join  (cost=311.50..160136.33 rows=1285252 width=46) (actual time=10.815..1438.885 rows=1404653 loops=1)
               Hash Cond: (foos.buzz_id = buzz.id)
               ->  Seq Scan on foos  (cost=0.00..131923.55 rows=4013004 width=32) (actual time=1.728..925.871 rows=4186572 loops=1)
                     Filter: (date <= GIVEN_DATE)
               ->  Hash  (cost=301.48..301.48 rows=801 width=18) (actual time=9.035..9.035 rows=736 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 47kB
                     ->  Index Scan using index_buzzes_on_name on buzzes  (cost=0.28..301.48 rows=801 width=18) (actual time=0.057..8.189 rows=736 loops=1)
                           Index Cond: ((name)::text = ANY (ARRAY_OF_NAMES::text[]))
                           Filter: ((group)::text = ANY (ARRAY_OF_GROUPS::text[]))
                           Rows Removed by Filter: 5
 Planning time: 5.804 ms
 Execution time: 4151.021 ms
(16 rows)

Heroku:

Unique  (cost=1086348.46..1086579.42 rows=17073 width=44) (actual time=64428.256..64980.542 rows=1467 loops=1)
   ->  Sort  (cost=1086348.46..1086463.94 rows=230962 width=44) (actual time=64428.254..64801.540 rows=1889788 loops=1)
         Sort Key: foos.buzz_id DESC, foos.date DESC
         Sort Method: external merge  Disk: 129240kB
         ->  Gather  (cost=3860.54..1082233.34 rows=230962 width=44) (actual time=20.290..61998.689 rows=1889788 loops=1)
               Workers Planned: 1
               Workers Launched: 1
               ->  Hash Join  (cost=2860.54..1058137.14 rows=135860 width=44) (actual time=16.240..61607.831 rows=944894 loops=2)
                     Hash Cond: (foos.buzz_id = buzz.id)
                     ->  Parallel Seq Scan on foos  (cost=0.00..953099.09 rows=28986598 width=32) (actual time=0.312..59310.999 rows=24527783 loops=2)
                           Filter: (date <= GIVEN_DATE)
                     ->  Hash  (cost=2855.15..2855.15 rows=1539 width=16) (actual time=9.022..9.022 rows=1467 loops=2)
                           Buckets: 2048  Batches: 1  Memory Usage: 90kB
                           ->  Bitmap Heap Scan on buzzes  (cost=973.85..2855.15 rows=1539 width=16) (actual time=6.534..8.753 rows=1467 loops=2)
                                 Recheck Cond: (((group)::text = ANY (ARRAY_OF_GROUPS::text[])) AND ((name)::text = ANY (ARRAY_OF_NAMES::text[])))
                                 Heap Blocks: exact=902
                                 ->  BitmapAnd  (cost=973.85..973.85 rows=1539 width=0) (actual time=6.416..6.416 rows=0 loops=2)
                                       ->  Bitmap Index Scan on index_buzzes_on_group  (cost=0.00..87.51 rows=10174 width=0) (actual time=1.059..1.059 rows=10504 loops=2)
                                             Index Cond: ((group)::text = ANY (ARRAY_OF_GROUPS::text[]))
                                       ->  Bitmap Index Scan on index_buzzes_on_name  (cost=0.00..886.14 rows=49668 width=0) (actual time=5.168..5.168 rows=50042 loops=2)
                                             Index Cond: ((name)::text = ANY (ARRAY_OF_NAMES::text[]))
 Planning time: 1.993 ms
 Execution time: 64999.534 ms

Do you have any suggestions for speeding up my query or is this expected behavior for my DB? I think I must be doing something wrong because I sincerely doubt that I'm pushing postgres to the edge of it performance.

Edit:

Running the query on Heroku before changing max_parallel_workers_per_gather to 0:

 Unique  (cost=1062472.92..1062683.75 rows=17073 width=36) (actual time=3307.233..3791.834 rows=1467 loops=1)
   Buffers: shared hit=8930, temp read=13401 written=13404
   ->  Sort  (cost=1062472.92..1062578.34 rows=210826 width=36) (actual time=3307.231..3602.232 rows=1890716 loops=1)
         Sort Key: data_points.data_series_id DESC, data_points.on_date DESC
         Sort Method: external merge  Disk: 107208kB
         Buffers: shared hit=8930, temp read=13401 written=13404
         ->  Gather  (cost=1966.01..1058744.32 rows=210826 width=36) (actual time=7.645..845.874 rows=1890716 loops=1)
               Workers Planned: 1
               Workers Launched: 1
               Buffers: shared hit=8929
               ->  Nested Loop  (cost=966.01..1036661.72 rows=124015 width=36) (actual time=3.888..396.710 rows=945358 loops=2)
                     Buffers: shared hit=75116
                     ->  Parallel Bitmap Heap Scan on data_series  (cost=965.89..2663.37 rows=826 width=16) (actual time=3.847..5.639 rows=734 loops=2)
                           Recheck Cond: (((column_code)::text = ANY ('{CAPEX,SHARESWA,price_close}'::text[])) AND ((ticker)::text = ANY ('{JPM,BAC,WFC,HSBC,BA,C,BUD,KO,DWDP,TM,PEP,RY,ABEV,HON,TD,UTX,ITUB,DEO,USB,AXP,WBK,HDB,BNS,PNC,UBS,ING,LYG,BMO,TSLA,BBD,BK,SMFG,GM,MON,HMC,BCS,COF,MFG,F,PX,RBS,STZ,FCAU,BBT,LYB,CM,STT,APD,KMB,STI,DB,MNST,PPG,MTB,DFS,IBN,BFB,SYF,RACE,PCAR,NTRS,APTV,IP,FITB,KEY,KB,RF,CFG,BAP,COL,DPS,IX,MGA,TTM,FMX,SHG,CCE,HBAN,CMA,TXT,TAP,BSAC,TDG,CE,KOF,EMN,WLK,ETFC,SIVB,LEA,ALLY,BCH,ALV,IFF,ALB,BWA,BAK,ZION,FBR,MOS,FMC,AVY,PPDF,CF,SPR,IEP,EWBC,HEI,CC,WF,HUN,AXTA,WBC,CFR,SHI,CIT,SEE,NYCB,BMA,BOKF,PII,PBCT,PACW,RPM,THO,WAL,GNTX,FHN,ADNT,CBSH,GT,CACC,SC,OZRK,SNV,ICL,HXL,ESLT,ALSN,OLN,OSK,PB,STL,FCNCA,CCU,SLM,PNFP,WBS,SMG,ISBC,AKOB,UMPQ,TCBI,WTFC,NEU,ERJ,MEOH,FIZZ,FNB,GRA,IBKC,HBHC,DLPH,BPOP,OMF,BKU,EEFT,VLY,DAN,HOMB,TFSL,BMS,CHFC,FHB,TCF,CBT,NAV,UMBF,VSM,UBSI,ASB,VC,KLXI,BOH,MBFI,TSE,KS,GDOT,POL,CATY,TEX,SSB,QD,FFIN,FULT,GBCI,SXT,NGVT,WAFD,COLB,TREE,PAH,UFS,BXS,KRO,SITE,LCII,CBU,TEN,BOFI,ONB,MTOR,SFNC,FMBI,NTB,LX,IBOC,SAM,CVBF,HAWK,MTX,SUPV,UBSH,FCB,FUL,TROX,UCBI,GWB,HOPE,BCPC,DORM,CADE,HTH,CSFL,FIBK,FDP,GCP,TRMK,CPS,VHI,SFBS,FBC,NNI,RNST,CALM,EGBN,VNTR,ROG,IBTX,LTXB,FRME,COT,ABCB,INDB,PPBI,MLHR,PQG,WSBC,BANR,FOE,WDFC,FFBC,REVG,COKE,CFFN,BANF,NSM,HNI,CTB,AXL,BHLB,NWBI,OEC,PFS,IOSP,HTLF,SCS,PRK,WABC,TNH,CVGW,KRA,NBTB,AIR,WSFS,WD,YRD,BUSE,FBP,SASR,STBA,FCF,SRCE,NP,HY,FSS,ASIX,SHLM,WNC,FBK,THRM,OCFC,TGI,BPFH,SBCF,BRKL,BNCL,WGO,SWM,MOD,EFSC,TMP,SBSI,LKFN,STBZ,BLX,SMP,ATRO,TSLX,CCC,EBSB,KMG,LOB,CASH,RYAM,CHCO,ECPG,TBK,AVAV,FBNC,KRNY,HFWA,BANC,CODI,NBHC,HAFC,WRLD,CNOB,MSFG,CUBI,AGM,NMFC,AGRO,WASH,GLT,ORIT,LBAI,SYBT,BMTC,MERC,CARO,CPF,COBZ,TCBK,GNBC,NXEO,RBCAA,UVSP,TRST,OCIP,GBNK,HMST,UBNK,GPRE,CURO,NCOM,FFIC,CTBI,GABC,NFBK,ENVA,FFWM,LDL,MSBI,CBTX,UEPS,HBNC,WLKP,LBC,FLIC,SBT,GSBC,RFP,PEBO,CAC,VBTX,BDGE,DCOM,SRI,BY,AVD,CSWI,TSC,PGC,HTBK,LION,HONE,QCRH,MBIN,TBBK,CLW,PUB,ANCX,FF,PFSI,CDXS,MBWM,EQBK,THFF,SPAR,FDEF,NCBS,TOWR,BHBK,CLXT,ABTX,BLBD,EBF,TCAP,WSBF,OFG,BMRC,IBCP,FISI,FRBK,HTBI,UCFC,EVRI,OCN,AROW,FCBC,REX,ACBI,BHB,FMBH,FSB,CCNE,REGI,CNBKA,OSBC,FBNK,RBB,BSRR,WTBA,CCBG,OLBK,MPAA,FMAO,FBMS,NL,HBCP,EBTC,BKCC,LXFR,SUP,FMNB,MOFG,SONA,BSF,LNDC,ONDK,GNTY,ATAX,BREW,FTK,CATC,PCSB,MCBC,RM,MCB,PHH,CSBK,PFIS,CVGI,UAN,MRLN,AMNB,AMRS,WNEB,INBK,SMBC,DCO,SMMF,ELVT,SFST,FNLC,FFKT,LMNR,BLMT}'::text[])))
                           Heap Blocks: exact=98
                           Buffers: shared hit=2552
                           ->  BitmapAnd  (cost=965.89..965.89 rows=1405 width=0) (actual time=6.007..6.007 rows=0 loops=1)
                                 Buffers: shared hit=1649
                                 ->  Bitmap Index Scan on index_data_series_on_column_code  (cost=0.00..80.30 rows=9364 width=0) (actual time=1.156..1.156 rows=10514 loops=1)
                                       Index Cond: ((column_code)::text = ANY ('{CAPEX,SHARESWA,price_close}'::text[]))
                                       Buffers: shared hit=47
                                 ->  Bitmap Index Scan on index_data_series_on_ticker  (cost=0.00..885.40 rows=49257 width=0) (actual time=4.663..4.663 rows=50077 loops=1)
                                       Index Cond: ((ticker)::text = ANY ('{JPM,BAC,WFC,HSBC,BA,C,BUD,KO,DWDP,TM,PEP,RY,ABEV,HON,TD,UTX,ITUB,DEO,USB,AXP,WBK,HDB,BNS,PNC,UBS,ING,LYG,BMO,TSLA,BBD,BK,SMFG,GM,MON,HMC,BCS,COF,MFG,F,PX,RBS,STZ,FCAU,BBT,LYB,CM,STT,APD,KMB,STI,DB,MNST,PPG,MTB,DFS,IBN,BFB,SYF,RACE,PCAR,NTRS,APTV,IP,FITB,KEY,KB,RF,CFG,BAP,COL,DPS,IX,MGA,TTM,FMX,SHG,CCE,HBAN,CMA,TXT,TAP,BSAC,TDG,CE,KOF,EMN,WLK,ETFC,SIVB,LEA,ALLY,BCH,ALV,IFF,ALB,BWA,BAK,ZION,FBR,MOS,FMC,AVY,PPDF,CF,SPR,IEP,EWBC,HEI,CC,WF,HUN,AXTA,WBC,CFR,SHI,CIT,SEE,NYCB,BMA,BOKF,PII,PBCT,PACW,RPM,THO,WAL,GNTX,FHN,ADNT,CBSH,GT,CACC,SC,OZRK,SNV,ICL,HXL,ESLT,ALSN,OLN,OSK,PB,STL,FCNCA,CCU,SLM,PNFP,WBS,SMG,ISBC,AKOB,UMPQ,TCBI,WTFC,NEU,ERJ,MEOH,FIZZ,FNB,GRA,IBKC,HBHC,DLPH,BPOP,OMF,BKU,EEFT,VLY,DAN,HOMB,TFSL,BMS,CHFC,FHB,TCF,CBT,NAV,UMBF,VSM,UBSI,ASB,VC,KLXI,BOH,MBFI,TSE,KS,GDOT,POL,CATY,TEX,SSB,QD,FFIN,FULT,GBCI,SXT,NGVT,WAFD,COLB,TREE,PAH,UFS,BXS,KRO,SITE,LCII,CBU,TEN,BOFI,ONB,MTOR,SFNC,FMBI,NTB,LX,IBOC,SAM,CVBF,HAWK,MTX,SUPV,UBSH,FCB,FUL,TROX,UCBI,GWB,HOPE,BCPC,DORM,CADE,HTH,CSFL,FIBK,FDP,GCP,TRMK,CPS,VHI,SFBS,FBC,NNI,RNST,CALM,EGBN,VNTR,ROG,IBTX,LTXB,FRME,COT,ABCB,INDB,PPBI,MLHR,PQG,WSBC,BANR,FOE,WDFC,FFBC,REVG,COKE,CFFN,BANF,NSM,HNI,CTB,AXL,BHLB,NWBI,OEC,PFS,IOSP,HTLF,SCS,PRK,WABC,TNH,CVGW,KRA,NBTB,AIR,WSFS,WD,YRD,BUSE,FBP,SASR,STBA,FCF,SRCE,NP,HY,FSS,ASIX,SHLM,WNC,FBK,THRM,OCFC,TGI,BPFH,SBCF,BRKL,BNCL,WGO,SWM,MOD,EFSC,TMP,SBSI,LKFN,STBZ,BLX,SMP,ATRO,TSLX,CCC,EBSB,KMG,LOB,CASH,RYAM,CHCO,ECPG,TBK,AVAV,FBNC,KRNY,HFWA,BANC,CODI,NBHC,HAFC,WRLD,CNOB,MSFG,CUBI,AGM,NMFC,AGRO,WASH,GLT,ORIT,LBAI,SYBT,BMTC,MERC,CARO,CPF,COBZ,TCBK,GNBC,NXEO,RBCAA,UVSP,TRST,OCIP,GBNK,HMST,UBNK,GPRE,CURO,NCOM,FFIC,CTBI,GABC,NFBK,ENVA,FFWM,LDL,MSBI,CBTX,UEPS,HBNC,WLKP,LBC,FLIC,SBT,GSBC,RFP,PEBO,CAC,VBTX,BDGE,DCOM,SRI,BY,AVD,CSWI,TSC,PGC,HTBK,LION,HONE,QCRH,MBIN,TBBK,CLW,PUB,ANCX,FF,PFSI,CDXS,MBWM,EQBK,THFF,SPAR,FDEF,NCBS,TOWR,BHBK,CLXT,ABTX,BLBD,EBF,TCAP,WSBF,OFG,BMRC,IBCP,FISI,FRBK,HTBI,UCFC,EVRI,OCN,AROW,FCBC,REX,ACBI,BHB,FMBH,FSB,CCNE,REGI,CNBKA,OSBC,FBNK,RBB,BSRR,WTBA,CCBG,OLBK,MPAA,FMAO,FBMS,NL,HBCP,EBTC,BKCC,LXFR,SUP,FMNB,MOFG,SONA,BSF,LNDC,ONDK,GNTY,ATAX,BREW,FTK,CATC,PCSB,MCBC,RM,MCB,PHH,CSBK,PFIS,CVGI,UAN,MRLN,AMNB,AMRS,WNEB,INBK,SMBC,DCO,SMMF,ELVT,SFST,FNLC,FFKT,LMNR,BLMT}'::text[]))
                                       Buffers: shared hit=1602
                     ->  Index Scan using index_data_points_on_data_series_id on data_points  (cost=0.11..1243.16 rows=2886 width=24) (actual time=0.011..0.378 rows=1289 loops=1467)
                           Index Cond: (data_series_id = data_series.id)
                           Filter: (on_date <= '2018-07-19 00:00:00'::timestamp without time zone)
                           Rows Removed by Filter: 0
                           Buffers: shared hit=72564
 Planning time: 2.019 ms
 Execution time: 3811.815 ms
(31 rows)

On Heroku after setting it to 0:

 Unique  (cost=1204266.73..1204477.55 rows=17073 width=36) (actual time=17099.325..17643.833 rows=1467 loops=1)
   Buffers: shared hit=147302 read=706897, temp read=13401 written=13404
   I/O Timings: read=1536.602
   ->  Sort  (cost=1204266.73..1204372.14 rows=210826 width=36) (actual time=17099.324..17454.268 rows=1890716 loops=1)
         Sort Key: data_points.data_series_id DESC, data_points.on_date DESC
         Sort Method: external merge  Disk: 107208kB
         Buffers: shared hit=147302 read=706897, temp read=13401 written=13404
         I/O Timings: read=1536.602
         ->  Hash Join  (cost=2741.18..1200538.12 rows=210826 width=36) (actual time=12.019..14304.386 rows=1890716 loops=1)
               Hash Cond: (data_points.data_series_id = data_series.id)
               Buffers: shared hit=147301 read=706897
               I/O Timings: read=1536.602
               ->  Seq Scan on data_points  (cost=0.00..1024116.26 rows=49271234 width=24) (actual time=0.092..9755.847 rows=49077572 loops=1)
                     Filter: (on_date <= '2018-07-19 00:00:00'::timestamp without time zone)
                     Rows Removed by Filter: 9468
                     Buffers: shared hit=144749 read=706897
                     I/O Timings: read=1536.602
               ->  Hash  (cost=2736.27..2736.27 rows=1405 width=16) (actual time=8.394..8.394 rows=1467 loops=1)
                     Buckets: 2048  Batches: 1  Memory Usage: 90kB
                     Buffers: shared hit=2552
                     ->  Bitmap Heap Scan on data_series  (cost=965.89..2736.27 rows=1405 width=16) (actual time=5.943..8.123 rows=1467 loops=1)
                           Recheck Cond: (((column_code)::text = ANY ('{CAPEX,SHARESWA,price_close}'::text[])) AND ((ticker)::text = ANY ('{JPM,BAC,WFC,HSBC,BA,C,BUD,KO,DWDP,TM,PEP,RY,ABEV,HON,TD,UTX,ITUB,DEO,USB,AXP,WBK,HDB,BNS,PNC,UBS,ING,LYG,BMO,TSLA,BBD,BK,SMFG,GM,MON,HMC,BCS,COF,MFG,F,PX,RBS,STZ,FCAU,BBT,LYB,CM,STT,APD,KMB,STI,DB,MNST,PPG,MTB,DFS,IBN,BFB,SYF,RACE,PCAR,NTRS,APTV,IP,FITB,KEY,KB,RF,CFG,BAP,COL,DPS,IX,MGA,TTM,FMX,SHG,CCE,HBAN,CMA,TXT,TAP,BSAC,TDG,CE,KOF,EMN,WLK,ETFC,SIVB,LEA,ALLY,BCH,ALV,IFF,ALB,BWA,BAK,ZION,FBR,MOS,FMC,AVY,PPDF,CF,SPR,IEP,EWBC,HEI,CC,WF,HUN,AXTA,WBC,CFR,SHI,CIT,SEE,NYCB,BMA,BOKF,PII,PBCT,PACW,RPM,THO,WAL,GNTX,FHN,ADNT,CBSH,GT,CACC,SC,OZRK,SNV,ICL,HXL,ESLT,ALSN,OLN,OSK,PB,STL,FCNCA,CCU,SLM,PNFP,WBS,SMG,ISBC,AKOB,UMPQ,TCBI,WTFC,NEU,ERJ,MEOH,FIZZ,FNB,GRA,IBKC,HBHC,DLPH,BPOP,OMF,BKU,EEFT,VLY,DAN,HOMB,TFSL,BMS,CHFC,FHB,TCF,CBT,NAV,UMBF,VSM,UBSI,ASB,VC,KLXI,BOH,MBFI,TSE,KS,GDOT,POL,CATY,TEX,SSB,QD,FFIN,FULT,GBCI,SXT,NGVT,WAFD,COLB,TREE,PAH,UFS,BXS,KRO,SITE,LCII,CBU,TEN,BOFI,ONB,MTOR,SFNC,FMBI,NTB,LX,IBOC,SAM,CVBF,HAWK,MTX,SUPV,UBSH,FCB,FUL,TROX,UCBI,GWB,HOPE,BCPC,DORM,CADE,HTH,CSFL,FIBK,FDP,GCP,TRMK,CPS,VHI,SFBS,FBC,NNI,RNST,CALM,EGBN,VNTR,ROG,IBTX,LTXB,FRME,COT,ABCB,INDB,PPBI,MLHR,PQG,WSBC,BANR,FOE,WDFC,FFBC,REVG,COKE,CFFN,BANF,NSM,HNI,CTB,AXL,BHLB,NWBI,OEC,PFS,IOSP,HTLF,SCS,PRK,WABC,TNH,CVGW,KRA,NBTB,AIR,WSFS,WD,YRD,BUSE,FBP,SASR,STBA,FCF,SRCE,NP,HY,FSS,ASIX,SHLM,WNC,FBK,THRM,OCFC,TGI,BPFH,SBCF,BRKL,BNCL,WGO,SWM,MOD,EFSC,TMP,SBSI,LKFN,STBZ,BLX,SMP,ATRO,TSLX,CCC,EBSB,KMG,LOB,CASH,RYAM,CHCO,ECPG,TBK,AVAV,FBNC,KRNY,HFWA,BANC,CODI,NBHC,HAFC,WRLD,CNOB,MSFG,CUBI,AGM,NMFC,AGRO,WASH,GLT,ORIT,LBAI,SYBT,BMTC,MERC,CARO,CPF,COBZ,TCBK,GNBC,NXEO,RBCAA,UVSP,TRST,OCIP,GBNK,HMST,UBNK,GPRE,CURO,NCOM,FFIC,CTBI,GABC,NFBK,ENVA,FFWM,LDL,MSBI,CBTX,UEPS,HBNC,WLKP,LBC,FLIC,SBT,GSBC,RFP,PEBO,CAC,VBTX,BDGE,DCOM,SRI,BY,AVD,CSWI,TSC,PGC,HTBK,LION,HONE,QCRH,MBIN,TBBK,CLW,PUB,ANCX,FF,PFSI,CDXS,MBWM,EQBK,THFF,SPAR,FDEF,NCBS,TOWR,BHBK,CLXT,ABTX,BLBD,EBF,TCAP,WSBF,OFG,BMRC,IBCP,FISI,FRBK,HTBI,UCFC,EVRI,OCN,AROW,FCBC,REX,ACBI,BHB,FMBH,FSB,CCNE,REGI,CNBKA,OSBC,FBNK,RBB,BSRR,WTBA,CCBG,OLBK,MPAA,FMAO,FBMS,NL,HBCP,EBTC,BKCC,LXFR,SUP,FMNB,MOFG,SONA,BSF,LNDC,ONDK,GNTY,ATAX,BREW,FTK,CATC,PCSB,MCBC,RM,MCB,PHH,CSBK,PFIS,CVGI,UAN,MRLN,AMNB,AMRS,WNEB,INBK,SMBC,DCO,SMMF,ELVT,SFST,FNLC,FFKT,LMNR,BLMT}'::text[])))
                           Heap Blocks: exact=903
                           Buffers: shared hit=2552
                           ->  BitmapAnd  (cost=965.89..965.89 rows=1405 width=0) (actual time=5.827..5.827 rows=0 loops=1)
                                 Buffers: shared hit=1649
                                 ->  Bitmap Index Scan on index_data_series_on_column_code  (cost=0.00..80.30 rows=9364 width=0) (actual time=0.884..0.884 rows=10514 loops=1)
                                       Index Cond: ((column_code)::text = ANY ('{CAPEX,SHARESWA,price_close}'::text[]))
                                       Buffers: shared hit=47
                                 ->  Bitmap Index Scan on index_data_series_on_ticker  (cost=0.00..885.40 rows=49257 width=0) (actual time=4.722..4.722 rows=50077 loops=1)
                                       Index Cond: ((ticker)::text = ANY ('{JPM,BAC,WFC,HSBC,BA,C,BUD,KO,DWDP,TM,PEP,RY,ABEV,HON,TD,UTX,ITUB,DEO,USB,AXP,WBK,HDB,BNS,PNC,UBS,ING,LYG,BMO,TSLA,BBD,BK,SMFG,GM,MON,HMC,BCS,COF,MFG,F,PX,RBS,STZ,FCAU,BBT,LYB,CM,STT,APD,KMB,STI,DB,MNST,PPG,MTB,DFS,IBN,BFB,SYF,RACE,PCAR,NTRS,APTV,IP,FITB,KEY,KB,RF,CFG,BAP,COL,DPS,IX,MGA,TTM,FMX,SHG,CCE,HBAN,CMA,TXT,TAP,BSAC,TDG,CE,KOF,EMN,WLK,ETFC,SIVB,LEA,ALLY,BCH,ALV,IFF,ALB,BWA,BAK,ZION,FBR,MOS,FMC,AVY,PPDF,CF,SPR,IEP,EWBC,HEI,CC,WF,HUN,AXTA,WBC,CFR,SHI,CIT,SEE,NYCB,BMA,BOKF,PII,PBCT,PACW,RPM,THO,WAL,GNTX,FHN,ADNT,CBSH,GT,CACC,SC,OZRK,SNV,ICL,HXL,ESLT,ALSN,OLN,OSK,PB,STL,FCNCA,CCU,SLM,PNFP,WBS,SMG,ISBC,AKOB,UMPQ,TCBI,WTFC,NEU,ERJ,MEOH,FIZZ,FNB,GRA,IBKC,HBHC,DLPH,BPOP,OMF,BKU,EEFT,VLY,DAN,HOMB,TFSL,BMS,CHFC,FHB,TCF,CBT,NAV,UMBF,VSM,UBSI,ASB,VC,KLXI,BOH,MBFI,TSE,KS,GDOT,POL,CATY,TEX,SSB,QD,FFIN,FULT,GBCI,SXT,NGVT,WAFD,COLB,TREE,PAH,UFS,BXS,KRO,SITE,LCII,CBU,TEN,BOFI,ONB,MTOR,SFNC,FMBI,NTB,LX,IBOC,SAM,CVBF,HAWK,MTX,SUPV,UBSH,FCB,FUL,TROX,UCBI,GWB,HOPE,BCPC,DORM,CADE,HTH,CSFL,FIBK,FDP,GCP,TRMK,CPS,VHI,SFBS,FBC,NNI,RNST,CALM,EGBN,VNTR,ROG,IBTX,LTXB,FRME,COT,ABCB,INDB,PPBI,MLHR,PQG,WSBC,BANR,FOE,WDFC,FFBC,REVG,COKE,CFFN,BANF,NSM,HNI,CTB,AXL,BHLB,NWBI,OEC,PFS,IOSP,HTLF,SCS,PRK,WABC,TNH,CVGW,KRA,NBTB,AIR,WSFS,WD,YRD,BUSE,FBP,SASR,STBA,FCF,SRCE,NP,HY,FSS,ASIX,SHLM,WNC,FBK,THRM,OCFC,TGI,BPFH,SBCF,BRKL,BNCL,WGO,SWM,MOD,EFSC,TMP,SBSI,LKFN,STBZ,BLX,SMP,ATRO,TSLX,CCC,EBSB,KMG,LOB,CASH,RYAM,CHCO,ECPG,TBK,AVAV,FBNC,KRNY,HFWA,BANC,CODI,NBHC,HAFC,WRLD,CNOB,MSFG,CUBI,AGM,NMFC,AGRO,WASH,GLT,ORIT,LBAI,SYBT,BMTC,MERC,CARO,CPF,COBZ,TCBK,GNBC,NXEO,RBCAA,UVSP,TRST,OCIP,GBNK,HMST,UBNK,GPRE,CURO,NCOM,FFIC,CTBI,GABC,NFBK,ENVA,FFWM,LDL,MSBI,CBTX,UEPS,HBNC,WLKP,LBC,FLIC,SBT,GSBC,RFP,PEBO,CAC,VBTX,BDGE,DCOM,SRI,BY,AVD,CSWI,TSC,PGC,HTBK,LION,HONE,QCRH,MBIN,TBBK,CLW,PUB,ANCX,FF,PFSI,CDXS,MBWM,EQBK,THFF,SPAR,FDEF,NCBS,TOWR,BHBK,CLXT,ABTX,BLBD,EBF,TCAP,WSBF,OFG,BMRC,IBCP,FISI,FRBK,HTBI,UCFC,EVRI,OCN,AROW,FCBC,REX,ACBI,BHB,FMBH,FSB,CCNE,REGI,CNBKA,OSBC,FBNK,RBB,BSRR,WTBA,CCBG,OLBK,MPAA,FMAO,FBMS,NL,HBCP,EBTC,BKCC,LXFR,SUP,FMNB,MOFG,SONA,BSF,LNDC,ONDK,GNTY,ATAX,BREW,FTK,CATC,PCSB,MCBC,RM,MCB,PHH,CSBK,PFIS,CVGI,UAN,MRLN,AMNB,AMRS,WNEB,INBK,SMBC,DCO,SMMF,ELVT,SFST,FNLC,FFKT,LMNR,BLMT}'::text[]))
                                       Buffers: shared hit=1602
 Planning time: 1.966 ms
 Execution time: 17665.205 ms
(34 rows)
  • It looks like your Heroku machine is just massively underpowered for the task you are assigning it. It is processing the "Seq Scan on foos" at 10 times fewer rows per millisecond than your local machine is. Can you assign it higher IO capacity? – jjanes Jul 21 '18 at 23:55
  • To simplify the comparison, can you set max_parallel_workers_per_gather=0 and collect a new explain analyze on the Heroku machine? Also, doing both with explain (analyze, buffers) could help. – jjanes Jul 21 '18 at 23:58
  • Added the new output to the question. I plan on upping the Heroku dynos and Postgres plan. Is there another way to increase IO capacity? – markain Jul 22 '18 at 0:31
  • I'm confused about "query on Heroku before changing max_parallel_workers_per_gather to 0" plan. Why did it change from your original Heroku plan, improving execution time by a factor of 17? Was the index index_data_points_on_data_series_id created between those executions, or did something else change? And is 3.8 seconds fast enough? – jjanes Jul 22 '18 at 0:42
  • The index was always there. I've been running the query a bunch so I think that the difference is just that the data is in the cache. – markain Jul 22 '18 at 0:46
0

An index on foos (buzz_id, date) should allow the index-scan in your 3.8 second query to turn into an index-only-scan. This should improve actual performance (at least if you keep foos well vacuumed) and more importantly is should make the plan look faster than it currently does and so stabilize your system on picking the now-even-faster-than 3.8 second plan rather than the 65 second one in a reliable way.

Once you have the index on (buzz_id, date) you should be able to get rid of the one solely on (buzz_id).

  • It looks like this knocked around 300ms off my query, 3.8 to 3.5. Thanks! – markain Jul 22 '18 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.