![]() ![]() Similarly, if Postgres is picking a different index instead of the index you think would be optimal, then this trick to make indexes invisible (to drop an index, run your query, and then rollback – all inside a transaction) shared by Haki Benita is a nice way to test this, in a non-production environment of course. If, however, it does now use the index you’d hoped, you now know that it is choosing not to. This does not, as the name might suggest, completely disable sequential scans, but rather discourages them by making the planner think they’d be a LOT more expensive than they really would be.Īs such, if after setting this to off, you still get a Seq Scan, there’s a really good chance that Postgres can’t use the index you want, and you’re seeing that category of issue. If you want to encourage an index scan in place of a sequential scan, you can try: Postgres has several parameters that let us temporarily discourage the planner’s choice of certain operations. But since PostgreSQL does not have such a feature, we’ll steer clear of that holy war and look at what we can do instead. ![]() To start with, you can try to disprove you’re in this category of issue by getting a query plan that does use the index.Īs I alluded to earlier, some other databases have a “query hints” feature that allows you to (pretty much) force the use of a specific index. The index type doesn’t support the operator in the query There are a few reasons Postgres won’t use a seemingly perfect index for a query.Ī datatype mismatch is preventing its use Checking whether Postgres can use the index The reasons that Postgres can’t use an index are more straightforward, and it’s also the easier of the two to rule out, so let’s start there. Sadly, we can’t tell from a single query plan which case we’re seeing, but through some investigating via multiple query plans, we can work out what’s going on. Working out which of these is the reason in your case is a great starting point. Either it can’t use the index, or it doesn’t think using the index will be faster. There are two main reasons that Postgres will not use an index. I’ll be assuming a basic familiarity with query plans (using EXPLAIN) and Postgres indexing, but honestly not much. If you’re somewhat new to Postgres, I hope that going through these will be interesting, as well as being useful next time you see an issue like this. Unlike some databases, you can’t force PostgreSQL to use a specific index, but there are several things you can do to work out what’s going on. So always be careful about NULL values in the column while using NOT IN operator.If you spend any amount of time working with Postgres, it’s likely that at some point you’re going to wonder why it is not using an index that you think it should be. SQL> select * from table_a where a not in (select * from table_b where b is not null) So how do we handle this? We will pick all the not null values from table table_b while using NOT IN operator. select * from table_a whara a 3 and a 4 and a null Since we are using an "AND" operator and anything other than true in any of the operand will not give me any output. The second query will be handled as below. select * from table_a whara a = 3 or a = 4 or a = null But my third operand a = null will neither evaluate to true nor false. So a null will not create a problem here as my first two operands will either evaluate to true or false. The first query will be handled internally something like this. But how come it's behaving like this? Let's split the two queries into "AND" and "OR" operator. We have introduced a null value in the table. ![]() The change is in the data of table table_b. The first query behaved as expected but what happened to the second query? Why didn't we get any output, what should have happened? Is there any difference in the query? No. SQL> select * from table_a where a not in (select * from table_b) SQL> select * from table_a where a in (select * from table_b) Now we will insert a null value in the table table_b and see how the above two queries behave. SQL> select * from table_a where a in (select * from table_b) Įxecute below query to check the non existence. Now we will execute a query to check the existence of a value in table_a by checking its value from table_b using IN operator. So I just created an environment to test this behavior. ![]() Actually I wanted to compare two tables and find out whether a value from table b existed in table a or not and find out its behavior if the column contains null values. Today I came across a very strange behaviour of query while using IN and NOT IN operators. Note: Since someone claimed that the external link is dead in Sushant Butta's answer I've posted the content here as a separate answer. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |