0

I have a partitioned table on which I run both queries and INSERTs simultaneously from multiple sessions. Database is Postgres 14.1.

Running select wait_event_type, wait_event, state, query from pg_stat_activity often reports my queries (plain SELECT on an indexed column) are waiting on DataFileWrite wait_event, type IO.

I don't understand, why would a query wait on file write?

Limit  (cost=210.00..210.00 rows=1 width=14) (actual time=3.279..3.281 rows=0 loops=1)
  Buffers: shared hit=27 read=7
  I/O Timings: read=3.044
  ->  Sort  (cost=210.00..210.11 rows=43 width=14) (actual time=3.278..3.280 rows=0 loops=1)
        Sort Key: mldata.sinceepoch DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=27 read=7
        I/O Timings: read=3.044
        ->  Append  (cost=0.56..209.79 rows=43 width=14) (actual time=3.274..3.276 rows=0 loops=1)
              Buffers: shared hit=27 read=7
              I/O Timings: read=3.044
              ->  Index Scan using mldata_313_destination_idx on mldata_313 mldata_1  (cost=0.56..20.67 rows=4 width=14) (actual time=0.056..0.056 rows=0 loops=1)
                    Index Cond: (destination = 154328765)
                    Filter: (day = ANY ('{313,314,315,316,317,318,319,320}'::integer[]))
                    Buffers: shared hit=6 read=1
                    I/O Timings: read=0.010
              ....... a few more of IndexScan .........
Planning:
  Buffers: shared hit=1340 read=136 written=11
  I/O Timings: read=0.910 **write=0.138**
Planning Time: 3.920 ms
Execution Time: 3.338 ms
milan
  • 2,355
  • 2
  • 23
  • 38
  • 1
    Check the execution plan using `explain (analyze, buffers)`. Maybe it uses temp buffers for sorting or something similar. It's also possible that the SELECT statement updates the visibility map –  Nov 15 '21 at 21:46
  • @a_horse_with_no_name Thanks for the hint. Added the execution plan. Indeed there is no writes in the plan when I remove sorting. – milan Nov 16 '21 at 11:10

0 Answers0