Skip to content

Instantly share code, notes, and snippets.

@thomasleese

thomasleese/original.sql

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

  • Select an option

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

Select an option

Learn more about clone URLs
Save thomasleese/effcf02e9c740e75fa36d9187622e044 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
original.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",
(SELECT json_agg((user_facing_version, state))
FROM editions e
WHERE e.document_id = documents.id
GROUP BY documents.content_id) AS state_history,
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
ORDER BY id ASC;
Raw
query_plan.txt
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Sort (cost=2855.38..2855.40 rows=6 width=688) (actual time=55254.576..55254.576 rows=1 loops=1)
Sort Key: subquery.id
Sort Method: quicksort Memory: 176kB
-> WindowAgg (cost=2855.14..2855.30 rows=6 width=688) (actual time=55254.513..55254.515 rows=1 loops=1)
-> Subquery Scan on subquery (cost=2855.14..2855.23 rows=6 width=688) (actual time=54963.879..55254.319 rows=1 loops=1)
-> Unique (cost=2855.14..2855.17 rows=6 width=867) (actual time=54963.875..55254.314 rows=1 loops=1)
-> Sort (cost=2855.14..2855.15 rows=6 width=867) (actual time=54963.856..55253.455 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: 710392kB
-> Nested Loop Left Join (cost=5.82..2855.06 rows=6 width=867) (actual time=1677.000..51075.560 rows=4701 loops=1)
-> Nested Loop Left Join (cost=5.40..79.83 rows=6 width=524) (actual time=5.101..81.719 rows=4701 loops=1)
-> Nested Loop (cost=4.98..77.12 rows=6 width=491) (actual time=5.065..28.487 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.102..0.104 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.55..68.52 rows=16 width=464) (actual time=4.958..24.546 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.55 rows=16 width=0) (actual time=3.885..3.885 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.009..0.009 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=343) (actual time=0.011..0.011 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.002..0.002 rows=1 loops=4701)
SubPlan 2
-> GroupAggregate (cost=0.43..68.80 rows=1 width=14) (actual time=10.769..10.769 rows=1 loops=4701)
-> Index Scan using index_editions_on_document_id on editions e (cost=0.43..68.71 rows=16 width=14) (actual time=0.009..4.592 rows=4701 loops=4701)
Index Cond: (document_id = documents.id)
SubPlan 3
-> Aggregate (cost=393.24..393.25 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=4701)
-> Index Scan using index_links_on_edition_id on links (cost=0.43..392.97 rows=107 width=32) (actual time=0.020..0.02
Sort Key: subquery.id
Sort Method: quicksort Memory: 176kB
-> WindowAgg (cost=2855.14..2855.30 rows=6 width=688) (actual time=55254.513..55254.515 rows=1 loops=1)
-> Subquery Scan on subquery (cost=2855.14..2855.23 rows=6 width=688) (actual time=54963.879..55254.319 rows=1 loops=1)
-> Unique (cost=2855.14..2855.17 rows=6 width=867) (actual time=54963.875..55254.314 rows=1 loops=1)
-> Sort (cost=2855.14..2855.15 rows=6 width=867) (actual time=54963.856..55253.455 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: 710392kB
-> Nested Loop Left Join (cost=5.82..2855.06 rows=6 width=867) (actual time=1677.000..51075.560 rows=4701 loops=1)
-> Nested Loop Left Join (cost=5.40..79.83 rows=6 width=524) (actual time=5.101..81.719 rows=4701 loops=1)
-> Nested Loop (cost=4.98..77.12 rows=6 width=491) (actual time=5.065..28.487 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.102..0.104 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.55..68.52 rows=16 width=464) (actual time=4.958..24.546 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.55 rows=16 width=0) (actual time=3.885..3.885 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.009..0.009 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=343) (actual time=0.011..0.011 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.002..0.002 rows=1 loops=4701)
SubPlan 2
-> GroupAggregate (cost=0.43..68.80 rows=1 width=14) (actual time=10.769..10.769 rows=1 loops=4701)
-> Index Scan using index_editions_on_document_id on editions e (cost=0.43..68.71 rows=16 width=14) (actual time=0.009..4.592 rows=4701 loops=4701)
Index Cond: (document_id = documents.id)
SubPlan 3
-> Aggregate (cost=393.24..393.25 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=4701)
-> Index Scan using index_links_on_edition_id on links (cost=0.43..392.97 rows=107 width=32) (actual time=0.020..0.020 rows=0 loops=4701)
Index Cond: (edition_id = editions.id)
Total runtime: 55393.339 ms
(34 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment