I have two tables:
create table animal
(
aid integer,
cid integer,
aname varchar(255) default NULL::character varying,
species text
);
create table daily_feeds
(
aid integer,
zid integer,
shift integer,
menu integer
);
and a query:
SELECT
aname,
shift
FROM animal
NATURAL JOIN daily_feeds
WHERE menu = 1;
The table animal contains around 40000 row, the table daily_feeds contains around 80000 row, 2 for each animal.
I thought that adding an index on the columns used for the join animal.aid and daily_feeds.aid could result in a different execution plan and improved performances.
create index aaid on animal (aid);
create index daid on daily_feeds using btree (aid);
This doesn't happen. Why is that?
Obviously this is a very simple and academic example that I'm trying to use to learn more about database query optimization.
EDIT:
Added execution plan when no indexes are present:
Hash Join (cost=1439.24..2488.23 rows=499 width=10) (actual time=11.701..55.260 rows=487 loops=1)
Hash Cond: (animal.aid = daily_feeds.aid)
-> Seq Scan on animal (cost=0.00..694.00 rows=40000 width=10) (actual time=0.007..20.775 rows=40000 loops=1)
-> Hash (cost=1433.00..1433.00 rows=499 width=8) (actual time=11.590..11.590 rows=487 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Seq Scan on daily_feeds (cost=0.00..1433.00 rows=499 width=8) (actual time=0.011..11.218 rows=487 loops=1)
Filter: (menu = 1)
Rows Removed by Filter: 79513
Planning time: 0.124 ms
Execution time: 55.521 ms
EDIT2: another question here:
Since I use two indexes I can also make them clustered indexes, physically ordering the table by the aid attribute.
cluster animal using aaid;
cluster daily_feeds using daid;
If I do that the Execution Plan doesn't change again. Isn't a Merge Join a more effective join strategy if both tables are sorted by the key column?
I'm not really sure about the behavior of Postgres here.
vacuum full analyze aaid;
vaccum full analyze daid
it removes dead tuples in the table by rewriting it and the index and it refreshes the planners statistics