Changes between Version 1 and Version 2 of QueryOptimization

Timestamp:

05/18/26 11:42:59 (9 days ago)

Author:

231159

Comment:

view 6 and 7 and summary

QueryOptimization

@@ -145 +145 @@
 * idx_user_prefrence_user on user_prefrence(user_id). Without this, PostgreSQL performs a linear sequential scan to find the user's preferences. With the index, it becomes a log-time lookup. The preference table is one-to-one with users, so this is a single-row lookup.
 * idx_notification_user_read on notification(user_id, is_read). The notification table can grow to millions of rows. This composite index allows PostgreSQL to quickly locate all notifications for a specific user and also evaluate the is_read = 0 condition using the index directly, without scanning the full notifications table. This single index was responsible for most of the performance gain.
+
+== View 6: vw_fact_check_summary
+
+** Purpose **
+
+This view provides a summarized fact-check overview for each article. It is primarily used by the admin panel and editorial dashboard to quickly see the fact-checking status of any article, how many checks have been run, how many resulted in each verdict (true, false, misleading), what the current approval status breakdown is, and when the most recent review occurred.
+
+** Design Decisions **
+
+* Filters WHERE fc.is_active = 1 so only active (non-retracted) fact checks are included in the summary.
+* Uses simple COUNT(CASE WHEN ...) expressions (not COUNT DISTINCT) since each fact check row has a unique id and a single verdict/status value. No deduplication is necessary.
+* Groups by article_id only — the simplest possible GROUP BY for this aggregation.
+* Returns MAX(reviewed_at) as last_reviewed_at so admins can see at a glance whether an article has been reviewed recently.
+
+** Performance & Indexing **
+
+||= Scenario =||= Before index =||= After index =||= Improvement =||
+||Fact check summary for article||   ~68ms   ||   -   ||  No index needed  ||
+
+This view performed well at baseline (68 ms) without requiring additional indexing. The is_active filter and article_id grouping are both served by the existing FK index on fact_checks(article_id). The fact_checks table is also significantly smaller than tables like article_views or notifications, so full-scan costs are inherently lower.
+
+== View 7: vw_article_metadata
+
+** Purpose **
+
+This view aggregates SEO and metadata for each article, specifically, the tags associated with it and the sources it cites. It is used by the application to build structured metadata (Open Graph tags, JSON-LD, sitemaps) and to display tag and source information on the article page. Keeping this separate from the article detail view keeps that view focused on content and avoids heavy STRING_AGG operations on every article detail load.
+
+** Design Decisions **
+
+* Uses STRING_AGG(DISTINCT t.name, ', ') and STRING_AGG(DISTINCT s.url, ', ') / STRING_AGG(DISTINCT s.title, ', ') to collapse multiple tags and sources into comma-separated strings, suitable for meta tag rendering without needing application-side join logic.
+* DISTINCT is used inside STRING_AGG to avoid duplicates that could arise from the cross-join between article_tags and article_source.
+* LEFT JOINs all four metadata tables (article_tags, tag, article_source, source) so articles with no tags or no sources still appear in the result.
+* Groups by a.id, a.slug, a.title, the minimal set needed to uniquely identify an article and return its key identifiers.
+
+** Performance & Indexing **
+
+||= Scenario =||= Before index =||= After index =||= Improvement =||
+||Article metadata by article_id||   ~230ms   ||   ~16ms   ||  ~93% faster  ||
+
+One index was added: idx_article_source_article on article_source(article_id). The article_source join table did not have an index on article_id, so PostgreSQL was performing a sequential scan across it to find sources for a given article. Adding this index turned that into a direct lookup, reducing the query from 230 ms to 16 ms — a 93% improvement. The article_tags table already had an FK index on article_id from the schema definition, so no additional index was needed there.
+
+== Summary
+
+||= View =||= Primary use =||= Before =||= After =||= Index =||
+||   vw_article_feed   ||   Homepage / category browse   ||   ~400 ms   ||   ~13 ms   ||   2 indexes   ||
+||   vw_article_detail   ||   Single article page   ||   ~66 ms   ||   ~40 ms   ||   1 index   ||
+||   vw_journalist_profile   ||   Journalist profile page   ||   ~2.7 s (v1) / ~2.0 s (v2)   ||   ~2.5 s (v1) / ~600 ms (v2)   ||   2 indexes + rewrite   ||
+||   vw_comment_thread   ||   Article comment section   ||   ~68 ms   ||   ~68 ms   ||   None   ||
+||   vw_user_dashboard   ||   User dashboard   ||   ~1600 ms   ||   ~15 ms   ||   2 indexes   ||
+||   vw_fact_check_summary   ||   Admin fact-check panel   ||   ~68 ms   ||   ~68 ms   ||   None   ||
+||   vw_article_metadata   ||   SEO / article metadata   ||   ~230 ms   ||   ~16 ms   ||   1 index   ||