Skip to content

Row Estimates

Row-Estimates

  • hash joins, merge joins
  • nested loops are good where the relation(no of rows) on one side is smaller.
  • check : analyse the table, collect statistics again.
  • we can analyse a single column
  • also happens as a part of auto vacuum
  • auto vacuum has three task:
    • vacuuming
    • prevent transaction from freezing
    • auto analyzing
    • maintaining free space map and visibility map
  • explain analyze and vacuum analyze are completely different
  • explain only doesn't help us to learn anything actually
  • Only Explain helps us to know how planner thinks about the data
  • we can check when analyze ran
    • all log entries should be there
    • pg_stat_user_tables
  • default_statistics_target -> increases show significant higher disk io
  • systematic approach - analyse depends on table size and no, of packets to analyse

holistic approach

  • if we increase no. of buckets the queries should not slow down.
  • any other parts of workload which struggle from our change and ensure that we changed something then other parts atleast remain the same.
  • query plans is where the mis-estimate happen.