Slowness when counting and joining with 2 big hypertables

TimescaleDB and PostgreSQL
1

Hi, this below query is running around 1 hour in postgres + timescale but 7 mins in sql server, may i know how to improve this query or any settings to optimize? Indexes are already created but it seems index scan is not used and seq scan is used for the big tables. There are more than 100M records in engine.sensormeasurementresult and 10M records in engine.devicemeasurementresult.
These 2 tables are hypertables paritioned by gatesenttime

SELECT count(*)
    FROM engine.sensormeasurementresult smr
     JOIN ref.fleetlocation fl ON fl.id = smr.fleetreferentialid and fl.isdeleted = false
     JOIN ref.vehicle v ON smr.vehiclereferentialid = v.id AND v.isactive = true AND v.isdeleted = false
     JOIN ref.device d ON smr.devicereferentialid = d.id
     JOIN master.devicetype dt ON d.type = dt.id
     join master.country c ON c.id = fl.country
     LEFT JOIN engine.devicemeasurementresult dmr on smr.messageid = dmr.messageid and dmr.towerid='M' and dmr.gatesenttime>= '2022-04-01'
     LEFT JOIN engine.vehicledirection vd ON vd.messageid = smr.messageid
     LEFT JOIN master.timezone tz ON fl.fleettimezone = tz.id
     where c.sbu='BSEMIA' and smr.gatesenttime >= '2022-04-01'

"Finalize Aggregate  (cost=5785809.13..5785809.14 rows=1 width=8) (actual time=2180920.395..2181585.644 rows=1 loops=1)"
"  Buffers: shared hit=370770 read=4301420 dirtied=815 written=3, temp read=292698 written=292704"
"  I/O Timings: read=2010787.359 write=0.500"
"  ->  Gather  (cost=5785808.50..5785809.11 rows=6 width=8) (actual time=2180920.390..2181585.639 rows=1 loops=1)"
"        Workers Planned: 6"
"        Workers Launched: 0"
"        Buffers: shared hit=370770 read=4301420 dirtied=815 written=3, temp read=292698 written=292704"
"        I/O Timings: read=2010787.359 write=0.500"
"        ->  Partial Aggregate  (cost=5784808.50..5784808.51 rows=1 width=8) (actual time=2180917.574..2180917.591 rows=1 loops=1)"
"              Buffers: shared hit=370770 read=4301420 dirtied=815 written=3, temp read=292698 written=292704"
"              I/O Timings: read=2010787.359 write=0.500"
"              ->  Parallel Hash Left Join  (cost=827606.51..5724691.23 rows=24046910 width=0) (actual time=2157660.283..2179449.261 rows=35634360 loops=1)"
"                    Hash Cond: ((smr_6.messageid)::text = (dmr_4.messageid)::text)"
"                    Buffers: shared hit=370770 read=4301420 dirtied=815 written=3, temp read=292698 written=292704"
"                    I/O Timings: read=2010787.359 write=0.500"
"                    ->  Parallel Hash Left Join  (cost=538939.68..5327267.81 rows=9727871 width=37) (actual time=2152248.252..2167370.537 rows=35632406 loops=1)"
"                          Hash Cond: ((smr_6.messageid)::text = (vd.messageid)::text)"
"                          Buffers: shared hit=149439 read=4301420 dirtied=815 written=3, temp read=292698 written=292704"
"                          I/O Timings: read=2010787.359 write=0.500"
"                          ->  Hash Join  (cost=98012.52..4633847.50 rows=9727871 width=37) (actual time=788.644..1662575.339 rows=35628680 loops=1)"
"                                Hash Cond: (d.type = dt.id)"
"                                Buffers: shared hit=149139 read=3960002 dirtied=692 written=3"
"                                I/O Timings: read=1550045.004 write=0.500"
"                                ->  Hash Join  (cost=98011.13..4601944.42 rows=9727871 width=41) (actual time=788.610..1655554.799 rows=35628680 loops=1)"
"                                      Hash Cond: (smr_6.devicereferentialid = d.id)"
"                                      Buffers: shared hit=149138 read=3960002 dirtied=692 written=3"
"                                      I/O Timings: read=1550045.004 write=0.500"
"                                      ->  Parallel Hash Join  (cost=97905.87..4576241.47 rows=9727871 width=41) (actual time=787.978..1646424.516 rows=35628680 loops=1)"
"                                            Hash Cond: (smr_6.vehiclereferentialid = v.id)"
"                                            Buffers: shared hit=149072 read=3960002 dirtied=692 written=3"
"                                            I/O Timings: read=1550045.004 write=0.500"
"                                            ->  Parallel Append  (cost=0.00..4377959.58 rows=20281701 width=45) (actual time=0.006..1616415.317 rows=121743609 loops=1)"
"                                                  Buffers: shared hit=60197 read=3960002 dirtied=692 written=3"
"                                                  I/O Timings: read=1550045.004 write=0.500"
"                                                  ->  Parallel Seq Scan on _hyper_2_520_chunk smr_6  (cost=0.00..749178.40 rows=3486672 width=45) (actual time=1.276..517451.456 rows=20924328 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=517 read=705078 dirtied=2"
"                                                        I/O Timings: read=506722.743"
"                                                  ->  Parallel Seq Scan on _hyper_2_512_chunk smr_4  (cost=0.00..731992.25 rows=3373140 width=45) (actual time=1.080..316017.184 rows=20271742 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=32 read=689796"
"                                                        I/O Timings: read=305629.254"
"                                                  ->  Parallel Seq Scan on _hyper_2_505_chunk smr  (cost=0.00..717673.54 rows=3380283 width=45) (actual time=0.759..205272.153 rows=20325600 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=87 read=675333 dirtied=86 written=1"
"                                                        I/O Timings: read=195650.154 write=0.122"
"                                                  ->  Parallel Seq Scan on _hyper_2_498_chunk smr_2  (cost=0.00..680900.01 rows=3261281 width=45) (actual time=0.099..250569.915 rows=19583380 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=36 read=640098 dirtied=41 written=2"
"                                                        I/O Timings: read=240770.001 write=0.378"
"                                                  ->  Parallel Seq Scan on _hyper_2_489_chunk smr_7  (cost=0.00..496488.21 rows=2531457 width=45) (actual time=0.124..154440.253 rows=15195289 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=69 read=464776 dirtied=35"
"                                                        I/O Timings: read=146742.910"
"                                                  ->  Parallel Seq Scan on _hyper_2_527_chunk smr_5  (cost=0.00..471806.97 rows=2246718 width=45) (actual time=0.830..96362.316 rows=13452111 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=59219 read=384504 dirtied=466"
"                                                        I/O Timings: read=90645.744"
"                                                  ->  Parallel Seq Scan on _hyper_2_482_chunk smr_3  (cost=0.00..428509.65 rows=2002149 width=45) (actual time=0.006..69417.662 rows=11991153 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Rows Removed by Filter: 1386122"
"                                                        Buffers: shared hit=235 read=400417 dirtied=62"
"                                                        I/O Timings: read=63884.197"
"                                                  ->  Parallel Seq Scan on _hyper_2_467_chunk smr_1  (cost=0.00..1.03 rows=2 width=45) (actual time=0.006..0.010 rows=4 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=1"
"                                                  ->  Parallel Seq Scan on _hyper_2_440_chunk smr_8  (cost=0.00..1.01 rows=1 width=45) (actual time=0.006..0.010 rows=2 loops=1)"
"                                                        Filter: (gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone)"
"                                                        Buffers: shared hit=1"
"                                            ->  Parallel Hash  (cost=95819.80..95819.80 rows=166886 width=4) (actual time=780.380..780.384 rows=445272 loops=1)"
"                                                  Buckets: 1048576  Batches: 1  Memory Usage: 25632kB"
"                                                  Buffers: shared hit=88875"
"                                                  ->  Parallel Hash Join  (cost=11503.34..95819.80 rows=166886 width=4) (actual time=94.370..663.247 rows=445272 loops=1)"
"                                                        Hash Cond: (v.fleetlocationid = fl.id)"
"                                                        Buffers: shared hit=88875"
"                                                        ->  Parallel Seq Scan on vehicle v  (cost=0.00..82537.42 rows=330851 width=8) (actual time=0.035..419.624 rows=1332194 loops=1)"
"                                                              Filter: (isactive AND (NOT isdeleted))"
"                                                              Rows Removed by Filter: 67795"
"                                                              Buffers: shared hit=79058"
"                                                        ->  Parallel Hash  (cost=10943.50..10943.50 rows=44787 width=4) (actual time=92.441..92.443 rows=65136 loops=1)"
"                                                              Buckets: 262144  Batches: 1  Memory Usage: 4608kB"
"                                                              Buffers: shared hit=9817"
"                                                              ->  Hash Join  (cost=7.25..10943.50 rows=44787 width=4) (actual time=0.093..77.395 rows=65136 loops=1)"
"                                                                    Hash Cond: (fl.country = c.id)"
"                                                                    Buffers: shared hit=9817"
"                                                                    ->  Parallel Seq Scan on fleetlocation fl  (cost=0.00..10701.91 rows=87627 width=12) (actual time=0.006..57.604 rows=267643 loops=1)"
"                                                                          Filter: (NOT isdeleted)"
"                                                                          Rows Removed by Filter: 3389"
"                                                                          Buffers: shared hit=9814"
"                                                                    ->  Hash  (cost=5.81..5.81 rows=115 width=4) (actual time=0.078..0.079 rows=115 loops=1)"
"                                                                          Buckets: 1024  Batches: 1  Memory Usage: 13kB"
"                                                                          Buffers: shared hit=3"
"                                                                          ->  Seq Scan on country c  (cost=0.00..5.81 rows=115 width=4) (actual time=0.017..0.059 rows=115 loops=1)"
"                                                                                Filter: ((sbu)::text = 'BSEMIA'::text)"
"                                                                                Rows Removed by Filter: 110"
"                                                                                Buffers: shared hit=3"
"                                      ->  Hash  (cost=83.45..83.45 rows=1745 width=8) (actual time=0.624..0.625 rows=1880 loops=1)"
"                                            Buckets: 2048  Batches: 1  Memory Usage: 90kB"
"                                            Buffers: shared hit=66"
"                                            ->  Seq Scan on device d  (cost=0.00..83.45 rows=1745 width=8) (actual time=0.010..0.400 rows=1880 loops=1)"
"                                                  Buffers: shared hit=66"
"                                ->  Hash  (cost=1.17..1.17 rows=17 width=4) (actual time=0.021..0.022 rows=17 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                      Buffers: shared hit=1"
"                                      ->  Seq Scan on devicetype dt  (cost=0.00..1.17 rows=17 width=4) (actual time=0.013..0.016 rows=17 loops=1)"
"                                            Buffers: shared hit=1"
"                          ->  Parallel Hash  (cost=374446.52..374446.52 rows=3272852 width=37) (actual time=473086.491..473086.492 rows=19555724 loops=1)"
"                                Buckets: 16777216  Batches: 2  Memory Usage: 580768kB"
"                                Buffers: shared hit=300 read=341418 dirtied=123, temp written=44416"
"                                I/O Timings: read=460742.355"
"                                ->  Parallel Seq Scan on vehicledirection vd  (cost=0.00..374446.52 rows=3272852 width=37) (actual time=0.008..465857.460 rows=19555724 loops=1)"
"                                      Buffers: shared hit=300 read=341418 dirtied=123"
"                                      I/O Timings: read=460742.355"
"                    ->  Parallel Hash  (cost=264545.33..264545.33 rows=1929720 width=37) (actual time=5356.146..5356.149 rows=7716711 loops=1)"
"                          Buckets: 8388608  Batches: 1  Memory Usage: 609472kB"
"                          Buffers: shared hit=221331"
"                          ->  Parallel Append  (cost=0.00..264545.33 rows=1929720 width=37) (actual time=0.013..2502.890 rows=7716711 loops=1)"
"                                Buffers: shared hit=221331"
"                                ->  Parallel Seq Scan on _hyper_3_514_chunk dmr_4  (cost=0.00..44074.80 rows=311829 width=37) (actual time=0.004..315.935 rows=1244977 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 128624"
"                                      Buffers: shared hit=38924"
"                                ->  Parallel Seq Scan on _hyper_3_522_chunk dmr_5  (cost=0.00..40628.32 rows=323189 width=37) (actual time=0.004..353.172 rows=1290826 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 145017"
"                                      Buffers: shared hit=35249"
"                                ->  Parallel Seq Scan on _hyper_3_507_chunk dmr_3  (cost=0.00..38520.82 rows=297958 width=37) (actual time=0.004..376.195 rows=1193687 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 138370"
"                                      Buffers: shared hit=33526"
"                                ->  Parallel Seq Scan on _hyper_3_492_chunk dmr_1  (cost=0.00..36645.22 rows=278084 width=37) (actual time=0.004..286.511 rows=1113877 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 114347"
"                                      Buffers: shared hit=32041"
"                                ->  Parallel Seq Scan on _hyper_3_485_chunk dmr  (cost=0.00..34884.57 rows=231316 width=37) (actual time=26.624..285.574 rows=926252 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 209505"
"                                      Buffers: shared hit=30623"
"                                ->  Parallel Seq Scan on _hyper_3_500_chunk dmr_2  (cost=0.00..34263.13 rows=290110 width=37) (actual time=0.005..368.793 rows=1161166 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 169002"
"                                      Buffers: shared hit=29275"
"                                ->  Parallel Seq Scan on _hyper_3_529_chunk dmr_6  (cost=0.00..25879.87 rows=254496 width=37) (actual time=0.011..246.029 rows=785926 loops=1)"
"                                      Filter: ((gatesenttime >= '2022-04-01 00:00:00'::timestamp without time zone) AND ((towerid)::text = 'M'::text))"
"                                      Rows Removed by Filter: 73945"
"                                      Buffers: shared hit=21693"
"Planning Time: 17.135 ms"
"Execution Time: 2181585.826 ms"
2

Can you please post the execution plan in code tags, so that the indentation is correct? It is really hard to read.

But a few general things so far. Sometimes it helps giving Postgres a hint that certain elements of the query should be done first. Like putting parts of it into a subquery or CTE. I tried to do that for your query, but not 100% it yields the same output (hard without demo data to verify). The query is all down below.

You have quite a bit of IO going on. TimescaleDB compression upon the hypertable may help speeding up the query due to less disk reads.

I see a few tables which may like to see an index (in case it’s not already there), like fleetlocation.

Possible query:

SELECT count(1)
FROM (
    SELECT smr.vehiclereferentialid,
           smr.devicereferentialid,
           smr.messageid
    FROM engine.sensormeasurementresult smr
    WHERE smr.gatesenttime >= '2022-04-01'
      AND smr.fleetreferentialid IN (
        SELECT fl.id
        FROM ref.fleetlocation fl
        JOIN master.country c ON c.id = fl.country
        WHERE fl.isdeleted = false
          AND c.sbu='BSEMIA'
      )
) smr
JOIN ref.vehicle v ON smr.vehiclereferentialid = v.id AND v.isactive = true AND v.isdeleted = false
JOIN ref.device d ON smr.devicereferentialid = d.id
JOIN master.devicetype dt ON d.type = dt.id
LEFT JOIN engine.devicemeasurementresult dmr on smr.messageid = dmr.messageid and dmr.towerid=‘M’ and dmr.gatesenttime>= '2022-04-01'
LEFT JOIN engine.vehicledirection vd ON vd.messageid = smr.messageid
LEFT JOIN ref.fleetlocation fl ON fl.id = smr.fleetreferentialid
LEFT JOIN master.timezone tz ON fl.fleettimezone = tz.id
3

Hi Esther, in case you’re confused, I made the edits for the code formatting. Hope you’re keeping well!

4

Thanks @LorraineP .

@noctarius i put select count just to simplify my select statement, but ultimately i want to select selected fields from each table in the join clause. so i cannot put a subquery like you mentioned. Question i want to ask is, why seq scan on smr chunks so long, it is also not using index scan, i know it maybe due to alot of data and seq scan maybe faster than index scan, but this is too long. How can I optimize it? Also, I’m using this query inside a function and there is no parallel processing which makes it even longer.

I’m using Azure Postgres with timescale and it does not have compression feature.