I just made a commit that changes the sections and latest blocks on the homepage from being ordered by explicit_published_at to last_published_at. This greatly reduced the sql query wait time because it means the whole query ArticlePage.objects.child_of(self).order_by(order).live() relies on fields from the wagtailcore_page table. I think that reduces the time either because it can use the wagtailcore_page index or because the expensive JOIN command between wagtailcore_page and article_articlepage is better optimized?
This causes a problem though because an article might be published and unpublished. The explicit_published_at will present the data editors want such an article to be displaying but after this change if last_published_at doesnt match explicit_published_at the article will be displayed out of order.
Possible solutions
Add a live field to article_articlepage that mirrors the live field of wagtailcore_page. That way we can index article_articlepage correctly and all our filtering and ordering can be done with the article_articlepage table. This seems weird though?
Move the explicit_published_at field to wagtailcore_page. I'm not even sure if that is possible
I just made a commit that changes the sections and latest blocks on the homepage from being ordered by explicit_published_at to last_published_at. This greatly reduced the sql query wait time because it means the whole query
ArticlePage.objects.child_of(self).order_by(order).live()
relies on fields from thewagtailcore_page
table. I think that reduces the time either because it can use thewagtailcore_page
index or because the expensive JOIN command betweenwagtailcore_page
andarticle_articlepage
is better optimized?This causes a problem though because an article might be published and unpublished. The
explicit_published_at
will present the data editors want such an article to be displaying but after this change iflast_published_at
doesnt matchexplicit_published_at
the article will be displayed out of order.Possible solutions
article_articlepage
that mirrors the live field ofwagtailcore_page
. That way we can indexarticle_articlepage
correctly and all our filtering and ordering can be done with thearticle_articlepage
table. This seems weird though?explicit_published_at
field towagtailcore_page
. I'm not even sure if that is possible