Skip to content

Instantly share code, notes, and snippets.

@thomasleese

thomasleese/new.sql

Last active September 6, 2018 08:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save thomasleese/71300c106cbc97c7691ec7906c30d3ce to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save thomasleese/71300c106cbc97c7691ec7906c30d3ce to your computer and use it in GitHub Desktop.
Download ZIP
Raw
new.sql
SELECT *,
COUNT(*) OVER () AS total
FROM
(SELECT DISTINCT ON(editions.document_id) editions.document_id,
"editions"."analytics_identifier",
editions.base_path AS base_path,
"editions"."content_store",
"editions"."description",
"editions"."details",
"editions"."document_type",
to_char(first_published_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS first_published_at,
to_char(last_edited_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS last_edited_at,
"editions"."phase",
to_char(public_updated_at, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS public_updated_at,
"editions"."publishing_app",
"editions"."redirects",
"editions"."rendering_app",
"editions"."routes",
"editions"."schema_name",
"editions"."title",
editions.user_facing_version AS user_facing_version,
"editions"."update_type",
editions.state AS publication_state,
documents.content_id AS content_id,
(SELECT CASE
WHEN unpublishings.edition_id IS NULL THEN NULL
ELSE row_to_json(unpublishing_data)
END
FROM (
VALUES (unpublishings.type,
unpublishings.explanation,
unpublishings.alternative_path,
unpublishings.redirects,
unpublishings.unpublished_at)) AS unpublishing_data(TYPE, explanation, alternative_path, redirects, unpublished_at)) AS unpublishing,
documents.locale AS locale,
documents.stale_lock_version AS lock_version,
"editions"."updated_at",
change_notes.note AS change_note,
(SELECT json_agg((links.link_type, links.target_content_id))
FROM links
WHERE links.edition_id = editions.id ) AS links,
"editions"."id"
FROM "editions"
INNER JOIN "documents" ON "documents"."id" = "editions"."document_id"
LEFT OUTER JOIN "change_notes" ON "change_notes"."edition_id" = "editions"."id"
LEFT OUTER JOIN "unpublishings" ON "unpublishings"."edition_id" = "editions"."id"
WHERE "documents"."content_id" = '08d48cdd-6b50-43ff-a53b-beab47f4aab0'
AND "documents"."locale" = 'en'
AND "editions"."state" IN ('draft',
'published',
'unpublished',
'superseded')
ORDER BY editions.document_id,
CASE state
WHEN 'draft' THEN 0
WHEN 'published' THEN 1
WHEN 'unpublished' THEN 1
WHEN 'superseded' THEN 2
END,
user_facing_version DESC) subquery
LEFT JOIN LATERAL (SELECT json_agg((user_facing_version, state)) AS state_history
FROM editions e
WHERE e.document_id = subquery.document_id
GROUP BY subquery.content_id) subquery2 ON true
ORDER BY id ASC
Raw
query_plan.txt
Sort (cost=2810.88..2810.90 rows=6 width=686) (actual time=158.837..158.838 rows=1 loops=1)
Sort Key: editions.id
Sort Method: quicksort Memory: 176kB
-> WindowAgg (cost=2374.00..2810.80 rows=6 width=686) (actual time=158.764..158.781 rows=1 loops=1)
-> Nested Loop Left Join (cost=2374.00..2810.73 rows=6 width=686) (actual time=149.269..158.443 rows=1 loops=1)
-> Unique (cost=2373.57..2373.60 rows=6 width=860) (actual time=134.887..144.061 rows=1 loops=1)
-> Sort (cost=2373.57..2373.58 rows=6 width=860) (actual time=134.886..143.753 rows=4701 loops=1)
Sort Key: editions.document_id, (CASE editions.state WHEN 'draft'::text THEN 0 WHEN 'published'::text THEN 1 WHEN 'unpublished'::text THEN 1 WHEN 'superseded'::text THEN 2 ELSE NULL::integer END), editions.user_facing_version
Sort Method: external merge Disk: 2552kB
-> Nested Loop Left Join (cost=5.83..2373.49 rows=6 width=860) (actual time=1.878..82.571 rows=4701 loops=1)
-> Nested Loop Left Join (cost=5.41..83.83 rows=6 width=518) (actual time=1.666..35.585 rows=4701 loops=1)
-> Nested Loop (cost=4.99..81.13 rows=6 width=485) (actual time=1.638..20.794 rows=4701 loops=1)
-> Index Scan using index_documents_on_content_id_and_locale on documents (cost=0.42..8.45 rows=1 width=27) (actual time=0.062..0.064 rows=1 loops=1)
Index Cond: ((content_id = '08d48cdd-6b50-43ff-a53b-beab47f4aab0'::uuid) AND ((locale)::text = 'en'::text))
-> Bitmap Heap Scan on editions (cost=4.56..72.51 rows=17 width=462) (actual time=1.568..19.362 rows=4701 loops=1)
Recheck Cond: (document_id = documents.id)
Filter: ((state)::text = ANY ('{draft,published,unpublished,superseded}'::text[]))
-> Bitmap Index Scan on index_editions_on_document_id_and_user_facing_version (cost=0.00..4.56 rows=17 width=0) (actual time=1.024..1.024 rows=4701 loops=1)
Index Cond: (document_id = documents.id)
-> Index Scan using index_change_notes_on_edition_id on change_notes (cost=0.42..0.44 rows=1 width=37) (actual time=0.003..0.003 rows=0 loops=4701)
Index Cond: (edition_id = editions.id)
-> Index Scan using index_unpublishings_on_edition_id on unpublishings (cost=0.42..0.45 rows=1 width=342) (actual time=0.002..0.002 rows=0 loops=4701)
Index Cond: (edition_id = editions.id)
SubPlan 1
-> Values Scan on "*VALUES*" (cost=0.00..0.02 rows=1 width=136) (actual time=0.001..0.001 rows=1 loops=4701)
SubPlan 2
-> Aggregate (cost=381.11..381.12 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=4701)
-> Index Scan using index_links_on_edition_id on links (cost=0.43..380.84 rows=108 width=32) (actual time=0.002..0.002 rows=0 loops=4701)
Index Cond: (edition_id = editions.id)
-> GroupAggregate (cost=0.43..72.82 rows=1 width=14) (actual time=14.378..14.378 rows=1 loops=1)
-> Index Scan using index_editions_on_document_id on editions e (cost=0.43..72.73 rows=17 width=14) (actual time=0.036..6.156 rows=4701 loops=1)
Index Cond: (document_id = editions.document_id)
Total runtime: 159.812 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment