Why is my index on the join keys not used in a very simple hash join case?
Providing information on the actual query plan used would help.

Speaking in general, the DB engine would have to use the two indexes to identify rows in both tables that have matching aid values; then, look up all the actual rows in the daily_feeds table to see if menu is 1, and then look up the rows in animal to get the aname values.

If there was a large number of rows to be returned, and if those are your complete tables, then it may well have decided it would be faster to simply load the tables in the first place, and scan them to find the matching rows and do the menu check (at which point aname is already there and available).



Let me note here that I'm most familiar with the way SQL Server uses indexes. It's possible that some of the following may not directly apply to PostgreSQL. However, most of the basics seem to match up, and I'm not going to delve into specifics enough to be likely to go too far wrong.

The optimizer has three ways to find rows in daily_feeds. It can simply look through the entire table; it can use the aid index to seek to a particular value or range of values of aid, then use the index information to go to the correct pages in the table to get the rows; or, it can treat the index as if it was a table, and scan that (possibly looking up the rows in the full table, if all the data it needs isn't in the index).

There are many consideration an optimizer must make in figuring out how to get the data it needs. One factor is the amount of pages it might have to load to get the data. Loading pages form disk is one of the most time-consuming tasks for a database. So, if it can limit the number of pages it may need, then that should speed up the query.

For this query, we need to find two columns in animal (aid and aname), and three in daily_feeds (aid, menu, shift).

It has two ways to identify the rows it's interested in: rows where an aid in animal matches an aid in daily_feeds, and rows where menu is 1. I'll guess that statistics would indicate (certainly a foreign key relationship would, if it actually existed) that all or almost all rows would be returned form both tables based on the aid = aid check. That doesn't do a good job of reducing the number of rows we're looking for. However, menu = 1 knocks out a lot of rows. Since menu isn't in an index, we have to go to the table to filter it by that.

Once we've got the daily_feeds rows we want, we need to find the matching rows in animal to pick up aname. We can expect to be looking for around 500 rows, and must assume (worst case) they're evenly distributed through the animal data. I'm make another assumption here; that (even with a text column) the rows in the animal table aren't terribly wide. If the basic table is stored in 500 pages (ignoring the text data; it may be stored off-row, but we don't need it for this query), then the odds are that all the pages will have to be loaded to get the rows we need.

Now, there are two paths to proceed:


Use the aaid index on animal to find the 500 aid values we're looking for; then, using the index, go to the pages where the data lives and find them in each page.
Load the animal table directly, and scan it for the 500 aid values we're looking for directly.


The optimizer decided to use option 2. Looking at it from this perspective, that doesn't necessarily seem that odd.

Note: the engine may be deciding this entire plan before it takes its first step. This can lead to bad plans (if, for instance, it turned out that menu = 1 only brought back 5 rows, then using the index might be faster) on occasion. SQL Server can maintain statistics on columns that help it determine cardinality (how well a known value for column X will limit the target rows), to help it make these decisions. PostgreSQL has some sort of statistic facility as well - not sure if it's directly comparable or not.