4 ms·

My team has been using Pganalyze (which has its own index advisor), and honestly, it's one of my favorite technical tools ever. I don't know how you could run a

My team has been using Pganalyze (which has its own index advisor), and honestly, it's one of my favorite technical tools ever. I don't know how you could run a large-scale postgres setup without it. In addition to index advice, it also shows top queries, vacuums, etc, and monitors for a whole host of other issues out of the box. They also have a ton of great content on their blog (5mins of postgres) where the founder will find blog posts by different authors/companies and analyze them in depth.

lfittl 3y ago
Thanks for the kind words! For anyone interested in how pganalyze's approach compares to this extension (and other alternatives like dexter, or using HypoPG directly), I gave a talk with my colleague Philippe last year at PgCon that describes how we use constraint programming and CP-SAT for dealing with the trade-off between index write overhead and read performance improvement, across multiple queries on a table: https://www.pgcon.org/events/pgcon_2023/schedule/session/422-automating-index-selection-using-constraint-programming/ https://www.pgcon.org/events/pgcon_2023/schedule/session/422...
- polivier 3y ago
  If anyone has some background in optimization and is interested in the more technical side of things, I gave a talk at JOPT 2023, comparing MIP and CP approaches for this problem. The slides can be found here: https://github.com/PhilippeOlivier/jopt2023 https://github.com/PhilippeOlivier/jopt2023
- Rapzid 3y ago
  This is awesome! Was the talk recording by chance?
  - lfittl 3y ago
    Yep, there is a "link to video" link on the talk page - here is the direct link: https://www.youtube.com/watch?v=pGN_pORKtSQ https://www.youtube.com/watch?v=pGN_pORKtSQ We also did a more recent webinar that has some slight revisions on top of that talk, recording available in our docs: https://pganalyze.com/docs/indexing-engine/cp-model https://pganalyze.com/docs/indexing-engine/cp-model
    - Rapzid 3y ago
      Cheers! I skimmed right over that.
deleted 3y ago
[deleted]

lfittl 3y ago

Thanks for the kind words! For anyone interested in how pganalyze's approach compares to this extension (and other alternatives like dexter, or using HypoPG directly), I gave a talk with my colleague Philippe last year at PgCon that describes how we use constraint programming and CP-SAT for dealing with the trade-off between index write overhead and read performance improvement, across multiple queries on a table: https://www.pgcon.org/events/pgcon_2023/schedule/session/422-automating-index-selection-using-constraint-programming/ https://www.pgcon.org/events/pgcon_2023/schedule/session/422...

polivier 3y ago
If anyone has some background in optimization and is interested in the more technical side of things, I gave a talk at JOPT 2023, comparing MIP and CP approaches for this problem. The slides can be found here: https://github.com/PhilippeOlivier/jopt2023 https://github.com/PhilippeOlivier/jopt2023
Rapzid 3y ago
This is awesome! Was the talk recording by chance?
- lfittl 3y ago
  Yep, there is a "link to video" link on the talk page - here is the direct link: https://www.youtube.com/watch?v=pGN_pORKtSQ https://www.youtube.com/watch?v=pGN_pORKtSQ We also did a more recent webinar that has some slight revisions on top of that talk, recording available in our docs: https://pganalyze.com/docs/indexing-engine/cp-model https://pganalyze.com/docs/indexing-engine/cp-model
  - Rapzid 3y ago
    Cheers! I skimmed right over that.

deleted 3y ago

[deleted]