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
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
aname), and three in
It has two ways to identify the rows it's interested in: rows where an
animal matches an
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 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.