Show HN: PostgreSQL index advisor (github.com)
This is a Postgres extension that can determine if a query should have an index. For example, for this table:
create table book(
id int primary key,
title text not null
);
select *
from index_advisor('select book.id from book where title = $1');
{"CREATE INDEX ON public.book USING btree (title)"}
It leans heavily on HypoPG[1], an excellent extension to determine if PostgreSQL will use a given index without spending resources to create them.
[0] pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.htm...
101 comments
[ 3.5 ms ] story [ 169 ms ] threadThe issue I’m struggling with these days is that I have an index that I want Postgres to use for one of the relations in a 3+ wide join, but unless I put a limit on a CTE of one of the tables, Postgres tries to run each join in parallel and ends up trying to join a bazillion rows like an asshole.
These days wrangling the query planner has me on the verge of breaking up with pg :-(
> This extension provides a mechanism, using which you can set your execution plan in stone; that is, Postgres will execute the plan you give it, or it will throw an error, but it will never try to guess a plan for you.
[0] https://github.com/DrPostgres/pg_plan_guarantee
https://github.com/ossc-db/pg_hint_plan
ps fighting the optimizer is the worst
> The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance
You get that feature in Azure SQL Database for $5/month.
I have used this in Azure SQL too, but according to that it should be in SQL Server.
https://learn.microsoft.com/en-us/sql/relational-databases/a...
> Automatic index management identifies indexes that should be added in your database, and indexes that should be removed. Applies to: Azure SQL Database
Just a few sentences later "Automatic tuning in Azure SQL Database also creates necessary indexes and drops unused indexes" - that's not in on-prem SQL Server.
Not perfect, because some queries may be more time-critical than others.
You could even annotate every query (INSERT and UPDATE as well as SELECT) with the dollar amount per execution you're willing to pay to make it 100ms faster, or accept to make it 100ms slower. Then let it know the marginal dollar cost of adding index storage, throw this all into a constraint solver and add the indexes which are compatible with your pricing.
Not just indexing, but table partitions, materialized views, keeping things in-memory...
Yes, but you need the context about what is the correct tradeoff for your use case. If you've got a service that depends on fast writes then adding latency via extra indices for improved read speed may not be an acceptable trade off. It depends on your application though.
Also, the problem with automatic indexing is that it only gets you so far, and any index can, in theory, mess up another query that is perfectly fine. Optimizers aren't omniscient. In addition, there are other knobs in the database, which affect performance. I suppose, a wider approach than just looking at indexes would be more successful. Like Ottertune, for example.
I definitely have seen the query planing make some peculiar choices in the past.
I think the main index type that bit me are the ones created by exclusion constraints. Often times it looks to the planner like "the right" index to use, but there is another (btree) that is way cheaper...the exclusion constraint is just there to ensure consistency.
In those cases to fix things, I added a WHERE clause to the index (e.g. WHERE 1=1), and the planner wouldn't consider that index unless it saw that same 1=1 condition in the queries WHERE clause.
Is there really such a thing as a bad query that can be rewritten to give the same results but faster? For me, that's already the query optimizer's job.
Of course there are "bad queries" where you query for things you don't need, join on the wrong columns, etc. And yeah the optimizer isn't perfect. But a query that you expect the query optimizer to "rewrite" and execute in an optimal way is a good query.
I can't tell if your disclaimer covers it but, yes, there are lots of bad queries that take a little bit of a re-write and run significantly faster. Generally it is someone taking a procedural vs set based approach or including things they don't need to try and help (adding an index to a temp table when it is only used once and going to be full scanned anyways). That's outside the general data typing/generally missing indexes.
For instance, we use SQLAnywhere at work (migrating to MSSQL), and it wasn't smart about IN (sub-query) so EXISTS was much faster.
Or, as I mentioned in another comment here, MSSQL performs much worse using a single OR in WHERE clause vs splitting into two queries and using UNION ALL, something which has no significant difference in SQLAnywhere.
For MSSQL I've found that even a dozen sub-queries in the SELECT part can be much faster than a single CROSS APPLY for fetching per-row data from another table.
Also the query might rely on certain assumptions that will in practice always hold in that application, but not in general. Especially around NULL, for example NOT IN vs NOT EXISTS[1].
[1]: https://www.mssqltips.com/sqlservertip/6013/sql-server-in-vs...
You can inject a hint into the query, forcing it to use a plan that would not otherwise be used, for example. Although, fixing a plan through a baseline is way cleaner. Mostly, I just meant that as an extreme example of something you can do, not something you should do. And yes, the only reason to re-write the query is when the query itself is bad in that it asks for unnecessary data or misses a join column. Admittedly, that's an extremely dirty and dangerous thing to do, as it uncouples app from db, but it is possible.
https://github.com/ankane/dexter
https://ankane.org/introducing-dexter
Also, the underlying extension, HypoPG, doesn't seem to collect any statistics on data to influence query planner.
My company is a paid customer since around 2020 and we are very satisfied, easily beats the Datadog's (which we use for the rest of our infra and apps) observability offering for PostgreSQL.
I used to think that performance issue in relational database was always a matter of :
* missing indexes * non-used indexes due to query order (where A, B instead of B, A)
But we had the case recently where we optimized a query in postgresql which was taking 100% of cpu during 1s (enough to trigger our alerting) by simply splitting a OR in two separate query.
So if you are looking for optimisation it may be good to know about "OR is bad". The two queries run in some ms both.
But "bad performance always due to indexes" gives a hint that you are somewhat new: No, bad performance in my experience was almost always due to developers either not understanding their ORM framework, or writing too expensive queries with or without index. Just adding indexes seldom solved the problem (maybe 1/5 of the time).
We write all our queries by hand. We've got decades of experience and I'd say we're pretty proficient.
For us adding an index is almost always the solution, assuming the statistics are fine.
Either we plain forgot, or a customer required new functionality we didn't predict so no index on the fields required.
Sure sometimes a poorly constructed query slips out or the optimizer needs some help by reorganizing the query, but it's rare.
SQLAnywhere handled the single OR fine, but we had to split the query into two using UNION ALL for MSSQL not to be slow as a snail burning tons of CPU.
No idea why the MSSQL optimizer doesn't do that itself, it's essentially what SQLAnywhere does.
1. Enable hypopg
2. Copy/paste the plpgsql file:https://github.com/supabase/index_advisor/blob/main/index_ad...
We are also developing the Trusted Language Extension with the RDS team, so at some point it should be easier to do this through database.dev:
https://database.dev/olirice/index_advisor
https://github.com/ankane/dexter
https://www.pingcap.com/blog/introducing-tiadvisor-automated...
Most meaningful extensions need to be compiled, installed, created dropped.
[0]: https://www.sqlai.ai/snippets/cluzdmi8w006d53gt82mguaga
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.
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...
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
Simple example, bigint column where all values would fit in smallint or if only 0/1 are present then boolean.
For a more complex idea if a large number of boolean columns are present in a table suggest packing them into integer/bigint as appropriate or bit(n) if individual querying/indexing via bit operators is needed.
There are many ways to claw back bytes on disk from PostgreSQL if you really need to and a lot of them could be suggested automatically.
The reason I say usefulness is harder to imagine is I don't know of anyone that would want to do this but wouldn't know how or where to look for these strategies. It's as if awareness of the need is commensurate with ability to resolve it.
If you mean across multiple tables the most obvious answer is a tool that helps improve normalisation.
Otherwise I'm not really sure what else you can mean by data structure that isn't how the data is stored on disk or how it's arranged into relations.
A tool could only recommend that the stored data would fit another datatype. But only business can tell you whether thats true.
I only see disadvantages or micro-optimizations.