Re: Many DataFileRead - IO waits - Mailing list pgsql-performance

From Michael Lewis
Subject Re: Many DataFileRead - IO waits
Date
Msg-id CAHOFxGrWoQ+o=aDYkeS50n9bgjJn2FCFrFXSsx5DbgUj=mh9Wg@mail.gmail.com
Whole thread Raw
In response to Many DataFileRead - IO waits  (Ben Snaidero <bensnaidero@geotab.com>)
Responses Re: Many DataFileRead - IO waits  (Ben Snaidero <bensnaidero@geotab.com>)
List pgsql-performance
How big is ix_tabledata_intid_timestampdate_intotherid3_intotherid2 on disk? If you create another index with same fields, how much space does it take? Real question- are you vacuuming aggressively enough for your workload? Your index name seems to indicate that intotherid3 would be the third key, and yet the planner chose not to scan that deep and instead filtered after it found the relevant tuples based on intid and timestampdate. That seems peculiar to me.

The documentation discourages multi-column indexes because they have limited application unless the same fields are always used. Personally, I don't love reviewing the stats of indexscans or how many tuples were fetched and having to guess how deeply the index was scanned for the various queries involved.

I'd wonder if an index on only intid_timestampdate would be both much smaller and also have a more right-leaning pattern of information being added and accessed in terms of keeping frequently needing blocks in shared buffers.

As a side note, that planning time seems high to me for such a simple query. Have you increased default_statistics_target significantly perhaps?

pgsql-performance by date:

Previous
From: Ben Snaidero
Date:
Subject: Many DataFileRead - IO waits
Next
From: Ben Snaidero
Date:
Subject: Re: Many DataFileRead - IO waits