Hello everyone,
I have inadvertently set off a furball on an unrelated ng on what is the
actual cost of SQL joins. But there's no reliable conclusion. I would
like to actually know that, that is, are JOINs truly expensive?
As they say, one measurement is worth a thousand opinions, so I've done
measurement on my PG app:
$ time echo "\c hrs;
SELECT hosts.ip, reservation.start_date, architecture.architecture,
os_kind.os_kind, os_rel.os_rel, os_version.os_version, project.project,
email.email FROM hosts
INNER JOIN project ON project.id = hosts.project_id
INNER JOIN architecture ON hosts.architecture_id = architecture.id
INNER JOIN os_kind ON os_kind.id = hosts.os_kind_id
INNER JOIN os_rel ON hosts.os_rel_id = os_rel.id
INNER JOIN os_version ON hosts.os_version_id = os_version.id
INNER JOIN reservation_hosts ON hosts.id = reservation_hosts.host_id
INNER JOIN reservation on reservation.id =
reservation_hosts.reservation_id
INNER JOIN email ON reservation.email_id = email.id
;" | psql > /dev/null
real 0m0.099s
user 0m0.015s
sys 0m0.005s
$ time echo "\c hrs;
> SELECT hosts.ip FROM hosts;
> SELECT reservation.start_date FROM reservation;
> SELECT architecture.architecture FROM architecture;
> SELECT os_rel.os_rel FROM os_rel;
> SELECT os_version.os_version FROM os_version;
> SELECT project.project FROM project;
> SELECT email.email FROM email;
> " | psql > /dev/null
real 0m0.046s
user 0m0.008s
sys 0m0.004s
Note: I've created indexes on those tables, both on data columns like
hosts.ip and on .id columns.
What do you think of this? And in general: when (if?) should one
denormalize data?
Regards,
mk
