I use PostgreSQL 9.4 and I have a table called
certificates containing the following columns:
CREATE TABLE certificates ( id serial PRIMARY KEY, common_name varchar(255) NOT NULL, state varchar(255), expires_on date, ); CREATE INDEX index_certificates_on_common_name ON certificates (common_name); CREATE INDEX index_certificates_on_expires_on ON certificates (expires_on);
I'd like to fetch all the certificates that have not been renewed yet, which means all the certificates that
- don't have another certificate matching the
- have another certificate matching
common_name, but the
stateis not issued or
I created the following query. It works, but I wonder if it can be optimized to be more efficient.
SELECT C.* FROM certificates as C WHERE C.expires_on - 30 <= 'date-of-today' AND ( SELECT COUNT(id) FROM certificates AS N WHERE certificates.common_name = N.common_name AND N.expires_on > certificates.expires_on AND N.state = 'issued' ) = 0;
Specifically, my concern is the nested query that I believe is run once for each record in C.
I tried by self-joining the table but the resulting query may return duplicates (e.g. if a certificate has 2 or more renewals) and I feel like using
GROUP BY may not be the best option.
SELECT C.id, N.id FROM certificates AS C LEFT JOIN certificates AS N ON C.common_name = N.common_name WHERE (C.expires_on - 30 <= 'date-of-today') AND (N.id IS NULL OR (N.state = 'issued' AND N.expires_on > C.expires_on));