Hi! First of all, thanks for taking the time to even look into this. I just found out most of the devs are actually in IRC rather than here.
Anyways, my goal is to create a sort of super table that lists the artists name, with any release_groups and the earliest release_dates attached to whatever album/single it finds since I don’t want it to list re-releases. To achieve this, I’m joining together the following tables:
release_event_dates is basically just a
release_unknown_country that turns the day, month and year columns into dates. I’ve included the code for the two views at the bottom of my post. This is the query I try to run just to see what’s new this week:
SELECT * FROM release_groups_single_date WHERE released >= '2018-01-16' AND released =< '2018-02-16'
I’m sure someone more experience can already see the problem in that it takes forever to execute. I can post the output from
EXPLAIN if you’d like, but the
EXPLAIN ANALYZE says it takes 15 seconds to execute. If I run a similar search on the website it returns results instantly.
Things I’ve tried
- Adding indexes where required
- Removing the
- Using Materialized views
The last one basically solved it for me, and I just run a cron job where it updates the view once a day. Still though, I’m very curious as to how the website achieves such good performance so I’ve been tinkering with the virtual machine but can’t seem to get the search-server docker to run for some reason.
Once I get the virtual machine search-server up and running, I intend to log all queries and dig through the website’s source to see what it’s doing and try to mimic. I’m a bit new at this, so everything takes forever and I would appreciate it if anyone could point me in the right direction.
Thanks for reading! Let me know if there’s any info you think I should add.
/edit After looking more closely at the search help page, it looks like it uses a search engine called Lucene, so I’m guessing that means it doesn’t query the database directly. There may not be an answer to my question then :x
CREATE VIEW release_event_dates AS
to_date(concat(COALESCE((q.date_year)::integer, 1900), '/', COALESCE((q.date_month)::integer, 1), '/', COALESCE((q.date_day)::integer, 1)), 'YYYY/MM/DD'::text) AS date,
FROM ( SELECT release_country.release,
NULL::integer AS int4
FROM release_unknown_country) q;
release.name AS rname,
min(release_event_dates.date) AS released
JOIN release ON release_group.id = release.release_group
JOIN release_event_dates ON release.id = release_event_dates.release
JOIN artist_credit ON artist_credit.id = release.artist_credit
JOIN artist_credit_name ON artist_credit.id = artist_credit_name.artist_credit
JOIN artist ON artist.id = artist_credit_name.artist
GROUP BY release_group.id, artist.name, release.name;