How do I force Postgres to use a particular index?
Solution 1
Assuming you're asking about the common "index hinting" feature found in many databases, PostgreSQL doesn't provide such a feature. This was a conscious decision made by the PostgreSQL team. A good overview of why and what you can do instead can be found here. The reasons are basically that it's a performance hack that tends to cause more problems later down the line as your data changes, whereas PostgreSQL's optimizer can re-evaluate the plan based on the statistics. In other words, what might be a good query plan today probably won't be a good query plan for all time, and index hints force a particular query plan for all time.
As a very blunt hammer, useful for testing, you can use the enable_seqscan
and enable_indexscan
parameters. See:
These are not suitable for ongoing production use. If you have issues with query plan choice, you should see the documentation for tracking down query performance issues. Don't just set enable_
params and walk away.
Unless you have a very good reason for using the index, Postgres may be making the correct choice. Why?
- For small tables, it's faster to do sequential scans.
- Postgres doesn't use indexes when datatypes don't match properly, you may need to include appropriate casts.
- Your planner settings might be causing problems.
See also this old newsgroup post.
Solution 2
Probably the only valid reason for using
set enable_seqscan=false
is when you're writing queries and want to quickly see what the query plan would actually be were there large amounts of data in the table(s). Or of course if you need to quickly confirm that your query is not using an index simply because the dataset is too small.
Solution 3
TL;DR
Run the following three commands and check whether the problem is fixed:
ANALYZE;
SET random_page_cost = 1.0;
SET effective_cache_size = 'X GB'; # replace X with total RAM size minus 2 GB
Read on for further details and background information about this.
Step 1: Analyze tables
As a simple first attempt to fix the issue, run the ANALYZE;
command as the database superuser in order to update all table statistics. From the documentation:
The query planner uses these statistics to help determine the most efficient execution plans for queries.
Step 2: Set the correct random page cost
Index scans require non-sequential disk page fetches. PostgreSQL uses the random_page_cost
configuration parameter to estimate the cost of such non-sequential fetches in relation to sequential fetches. From the documentation:
Reducing this value [...] will cause the system to prefer index scans; raising it will make index scans look relatively more expensive.
The default value is 4.0
, thus assuming an average cost factor of 4 compared to sequential fetches, taking caching effects into account. However, if your database is stored on an SSD drive, then you should actually set random_page_cost
to 1.1
according to the documentation:
Storage that has a low random read cost relative to sequential, e.g., solid-state drives, might also be better modeled with a lower value for
random_page_cost
, e.g.,1.1
.
Also, if an index is mostly (or even entirely) cached in RAM, then an index scan will always be significantly faster than a disk-served sequential scan. The query planner however doesn't know which parts of the index are already cached, and thus might make an incorrect decision.
If your database indices are frequently used, and if the system has sufficient RAM, then the indices are likely to be cached eventually. In such a case, random_page_cost
can be set to 1.0
, or even to a value below 1.0
to aggressively prefer using index scans (although the documentation advises against doing that). You'll have to experiment with different values and see what works for you.
As a side note, you could also consider using the pg_prewarm extension to explicitly cache your indices into RAM.
You can set the random_page_cost
like this:
SET random_page_cost = 1.0;
Step 3: Set the correct cache size
On a system with 8 or more GB RAM, you should set the effective_cache_size
configuration parameter to the amount of memory which is typically available to PostgreSQL for data caching. From the documentation:
A higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used.
Note that this parameter doesn't change the amount of memory which PostgreSQL will actually allocate, but is only used to compute cost estimates. A reasonable value (on a dedicated database server, at least) is the total RAM size minus 2 GB. The default value is 4 GB
.
You can set the effective_cache_size
like this:
SET effective_cache_size = '14 GB'; # e.g. on a dedicated server with 16 GB RAM
Step 4: Fix the problem permanently
You probably want to use ALTER SYSTEM SET ...
or ALTER DATABASE db_name SET ...
to set the new configuration parameter values permanently (either globally or per-database). See the documentation for details about setting parameters.
Step 5: Additional resources
If it still doesn't work, then you might also want to take a look at this PostgreSQL Wiki page about server tuning.
Solution 4
Sometimes PostgreSQL fails to make the best choice of indexes for a particular condition. As an example, suppose there is a transactions table with several million rows, of which there are several hundred for any given day, and the table has four indexes: transaction_id, client_id, date, and description. You want to run the following query:
SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
description = 'Refund'
GROUP BY client_id
PostgreSQL may choose to use the index transactions_description_idx instead of transactions_date_idx, which may lead to the query taking several minutes instead of less than one second. If this is the case, you can force using the index on date by fudging the condition like this:
SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
description||'' = 'Refund'
GROUP BY client_id
Solution 5
The question on itself is very much invalid. Forcing (by doing enable_seqscan=off for example) is very bad idea. It might be useful to check if it will be faster, but production code should never use such tricks.
Instead - do explain analyze of your query, read it, and find out why PostgreSQL chooses bad (in your opinion) plan.
There are tools on the web that help with reading explain analyze output - one of them is explain.depesz.com - written by me.
Another option is to join #postgresql channel on freenode irc network, and talking to guys there to help you out - as optimizing query is not a matter of "ask a question, get answer be happy". it's more like a conversation, with many things to check, many things to be learned.
mike
Updated on January 21, 2022Comments
-
mike over 2 years
How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan?
-
Grigory Kislin about 7 yearsDuplicated, see stackoverflow.com/questions/14554302/…
-
collimarco about 4 years+1 I would love to see this feature. It's not a matter of simply disabling seq scan, as other answers say: we need the ability to force PG to use a specific index. This is because in the real word stats can be completely wrong and at that point you need to use unreliable / partial workarounds. I agree that in simple cases you should first check the indexes and other settings, but for reliability and advanced uses on big data we need this.
-
Kevin Parker about 4 yearsMySQL and Oracle both have it... Not sure why Postgres' planner is so unreliable.
-
-
Kent Fredric over 15 yearsAgreed, Forcing postgres to do it your way usually means you've done it wrong. 9/10 Times the planner will beat anything you can come up with. The other 1 time its because you made it wrong.
-
metdos over 11 yearsI think it is a good idea for checking really operator classes of your index hold.
-
dwery about 10 yearsthis short reply actually gives a good hint for testing purposes
-
Ivailo Bardarov about 10 yearsNo one is answering the question!
-
waffl almost 10 yearsI hate to revive an old question but I see often in Postgres documentation, discussions and here, but is there a generalized concept for what qualifies for a small table? Is it something like 5000 rows, or 50000 etc?
-
jpmc26 over 9 years@waffl Have you considered benchmarking? Create a simple table with an index and an accompanying function for filling it up with n rows of random junk. Then start looking at the query plan for different values of n. When you see it start using the index, you should have a ballpark answer. You can also get sequential scans if PostgreSQL determines (based on statistics) that an index scan isn't going to eliminate very many rows, too. So benchmarking is always a good idea when you have real performance concerns. As an off-hand, anecdotal guess, I'd say a couple thousand is usually "small."
-
jpmc26 over 9 years@IvailoBardarov The reason all these other suggestions are here is because PostgreSQL doesn't have this feature; this was a conscious decision made by the developers based on how it's typically used and the long term problems it causes.
-
Brian Hellekin about 6 yearsA nice trick to test: run
set enable_seqscan=false
, run your query, and then quickly runset enable_seqscan=true
to return postgresql to its proper behaviour (and obviously don't do this in production, only in development!) -
Agnius Vasiliauskas almost 6 yearsNice idea. However, when we disable current index usage with this method - postgresql query optimizer fallbacks to next suitable index. Thus, no guarantee that optimizer will choose
your_wanted_index
, it can be so that postgresql engine will just perform a sequence / primary key scan instead. Conclusion - there is no 100% reliable method to force some index usage for PostgreSql server. -
Guido Leenders over 5 yearsWith over 30 years of experience on platforms such as Oracle, Teradata and MSSQL, I find the optimizer of PostgreSQL 10 not especially smart. Even with up-to-date statistics it generates less efficient execution plans than forced in a special direction. Providing structural hints to compensate these issues would provide a solution to allow PostgreSQL to grow in more market segments. IMHO.
-
Guido Leenders over 5 yearsNice trick. Although a good optimizer should of course optimize away the offset 0 :-)
-
Anatoly Alekseev over 5 yearsI even had to set random_page_cost = 0.1 in order to make index scan work on large (~600M rows table) in Pg 10.1 on Ubuntu. Without the tweak, seq scan (despite being parallel) was taking 12 mins (Note that Analyze table was performed!). Drive is SSD. After the tweak, exec time became 1 second.
-
Izkata over 4 years@BrianHellekin Better,
SET SESSION enable_seqscan=false
to only affect yourself -
Surya over 4 yearsWhat if there is no
where
condition but two tables or joined and Postgres fails to take the index. -
Ezequiel Tolnay over 4 years@Surya the above applies to both WHERE and to JOIN ... ON conditions
-
Julien almost 4 yearsYou saved my day. I was going crazy trying to figure out how the exact same query on the same database was taking 30 seconds on one machine and less than 1 on another, even after running analyze on both ends... To whom it may concern : the command 'ALTER SYSTEM SET random_page_cost=x' sets the new default value globally.
-
Pascal Heraud over 3 yearsSESSION is the default so it's equivalent to set enable_seqscan=false
-
Michael Goldshteyn almost 3 yearsThis is bar far the best answer or at least the first thing to try when an index is not being used.