Explain Django-include

Prefetch guids

In [112]: str(OSFUser.objects.filter().prefetch_related('guids').query)
Out[112]: 'SELECT "osf_osfuser"."id", "osf_osfuser"."password", "osf_osfuser"."last_login", "osf_osfuser"."is_superuser", "osf_osfuser"."created", "osf_osfuser"."modified", "osf_osfuser"."content_type_pk", "osf_osfuser"."spam_status", "osf_osfuser"."spam_pro_tip", "osf_osfuser"."spam_data", "osf_osfuser"."date_last_reported", "osf_osfuser"."reports", "osf_osfuser"."username", "osf_osfuser"."fullname", "osf_osfuser"."is_registered", "osf_osfuser"."security_messages", "osf_osfuser"."is_invited", "osf_osfuser"."unclaimed_records", "osf_osfuser"."contributor_added_email_records", "osf_osfuser"."member_added_email_records", "osf_osfuser"."group_connected_email_records", "osf_osfuser"."merged_by_id", "osf_osfuser"."verification_key", "osf_osfuser"."verification_key_v2", "osf_osfuser"."email_last_sent", "osf_osfuser"."change_password_last_attempt", "osf_osfuser"."old_password_invalid_attempts", "osf_osfuser"."email_verifications", "osf_osfuser"."mailchimp_mailing_lists", "osf_osfuser"."osf_mailing_lists", "osf_osfuser"."date_registered", "osf_osfuser"."given_name", "osf_osfuser"."middle_names", "osf_osfuser"."family_name", "osf_osfuser"."suffix", "osf_osfuser"."external_identity", "osf_osfuser"."jobs", "osf_osfuser"."schools", "osf_osfuser"."social", "osf_osfuser"."date_last_login", "osf_osfuser"."date_confirmed", "osf_osfuser"."date_disabled", "osf_osfuser"."deleted", "osf_osfuser"."comments_viewed_timestamp", "osf_osfuser"."timezone", "osf_osfuser"."locale", "osf_osfuser"."requested_deactivation", "osf_osfuser"."contacted_deactivation", "osf_osfuser"."notifications_configured", "osf_osfuser"."accepted_terms_of_service", "osf_osfuser"."chronos_user_id", "osf_osfuser"."department", "osf_osfuser"."is_active", "osf_osfuser"."is_staff", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_guid"."modified", "osf_guid"."id", "osf_guid"."_id", "osf_guid"."content_type_id", "osf_guid"."object_id", "osf_guid"."created")) AS "__fields" FROM "osf_guid" WHERE ("osf_guid"."object_id" = osf_osfuser."id") AND ("osf_guid"."content_type_id" = 1)) AS "__guids" FROM "osf_osfuser"'

In [116]: pprint.pprint(OSFUser.objects.filter().prefetch_related('guids').explain())
('Seq Scan on osf_osfuser  (cost=0.00..91.92 rows=10 width=6099)\n'
 '  SubPlan 1\n'
 '    ->  Aggregate  (cost=8.17..8.18 rows=1 width=32)\n'
 '          ->  Index Scan using osf_guid_content_type_id_5ed9fdc2 on '
 'osf_guid  (cost=0.14..8.16 rows=1 width=544)\n'
 '                Index Cond: (content_type_id = 1)\n'
 '                Filter: (object_id = osf_osfuser.id)')


Django-include guids

In [113]: str(OSFUser.objects.filter().include('guids').query)
Out[113]: 'SELECT "osf_osfuser"."id", "osf_osfuser"."password", "osf_osfuser"."last_login", "osf_osfuser"."is_superuser", "osf_osfuser"."created", "osf_osfuser"."modified", "osf_osfuser"."content_type_pk", "osf_osfuser"."spam_status", "osf_osfuser"."spam_pro_tip", "osf_osfuser"."spam_data", "osf_osfuser"."date_last_reported", "osf_osfuser"."reports", "osf_osfuser"."username", "osf_osfuser"."fullname", "osf_osfuser"."is_registered", "osf_osfuser"."security_messages", "osf_osfuser"."is_invited", "osf_osfuser"."unclaimed_records", "osf_osfuser"."contributor_added_email_records", "osf_osfuser"."member_added_email_records", "osf_osfuser"."group_connected_email_records", "osf_osfuser"."merged_by_id", "osf_osfuser"."verification_key", "osf_osfuser"."verification_key_v2", "osf_osfuser"."email_last_sent", "osf_osfuser"."change_password_last_attempt", "osf_osfuser"."old_password_invalid_attempts", "osf_osfuser"."email_verifications", "osf_osfuser"."mailchimp_mailing_lists", "osf_osfuser"."osf_mailing_lists", "osf_osfuser"."date_registered", "osf_osfuser"."given_name", "osf_osfuser"."middle_names", "osf_osfuser"."family_name", "osf_osfuser"."suffix", "osf_osfuser"."external_identity", "osf_osfuser"."jobs", "osf_osfuser"."schools", "osf_osfuser"."social", "osf_osfuser"."date_last_login", "osf_osfuser"."date_confirmed", "osf_osfuser"."date_disabled", "osf_osfuser"."deleted", "osf_osfuser"."comments_viewed_timestamp", "osf_osfuser"."timezone", "osf_osfuser"."locale", "osf_osfuser"."requested_deactivation", "osf_osfuser"."contacted_deactivation", "osf_osfuser"."notifications_configured", "osf_osfuser"."accepted_terms_of_service", "osf_osfuser"."chronos_user_id", "osf_osfuser"."department", "osf_osfuser"."is_active", "osf_osfuser"."is_staff", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_guid"."modified", "osf_guid"."id", "osf_guid"."_id", "osf_guid"."content_type_id", "osf_guid"."object_id", "osf_guid"."created")) AS "__fields" FROM "osf_guid" WHERE ("osf_guid"."object_id" = osf_osfuser."id") AND ("osf_guid"."content_type_id" = 1)) AS "__guids" FROM "osf_osfuser"'

In [118]: pprint.pprint(OSFUser.objects.filter().include('guids').explain())
('Seq Scan on osf_osfuser  (cost=0.00..91.92 rows=10 width=6099)\n'
 '  SubPlan 1\n'
 '    ->  Aggregate  (cost=8.17..8.18 rows=1 width=32)\n'
 '          ->  Index Scan using osf_guid_content_type_id_5ed9fdc2 on '
 'osf_guid  (cost=0.14..8.16 rows=1 width=544)\n'
 '                Index Cond: (content_type_id = 1)\n'
 '                Filter: (object_id = osf_osfuser.id)')

Include Node with contributor guids

In [134]: str(Node.objects.include('guids', 'contributor__user__guids').query)
Out[134]: 'SELECT "osf_abstractnode"."id", "osf_abstractnode"."created", "osf_abstractnode"."modified", "osf_abstractnode"."content_type_pk", "osf_abstractnode"."spam_status", "osf_abstractnode"."spam_pro_tip", "osf_abstractnode"."spam_data", "osf_abstractnode"."date_last_reported", "osf_abstractnode"."reports", "osf_abstractnode"."last_logged", "osf_abstractnode"."title", "osf_abstractnode"."description", "osf_abstractnode"."category", "osf_abstractnode"."node_license_id", "osf_abstractnode"."registration_responses", "osf_abstractnode"."registration_responses_migrated", "osf_abstractnode"."type", "osf_abstractnode"."child_node_subscriptions", "osf_abstractnode"."creator_id", "osf_abstractnode"."deleted_date", "osf_abstractnode"."deleted", "osf_abstractnode"."file_guid_to_share_uuids", "osf_abstractnode"."forked_date", "osf_abstractnode"."forked_from_id", "osf_abstractnode"."is_fork", "osf_abstractnode"."is_public", "osf_abstractnode"."is_deleted", "osf_abstractnode"."access_requests_enabled", "osf_abstractnode"."custom_citation", "osf_abstractnode"."comment_level", "osf_abstractnode"."root_id", "osf_abstractnode"."article_doi", "osf_abstractnode"."custom_storage_usage_limit_public", "osf_abstractnode"."custom_storage_usage_limit_private", "osf_abstractnode"."piwik_site_id", "osf_abstractnode"."suspended", "osf_abstractnode"."template_node_id", "osf_abstractnode"."wiki_private_uuids", "osf_abstractnode"."keenio_read_key", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_guid"."modified", "osf_guid"."id", "osf_guid"."_id", "osf_guid"."content_type_id", "osf_guid"."object_id", "osf_guid"."created")) AS "__fields" FROM "osf_guid" WHERE ("osf_guid"."object_id" = osf_abstractnode."id") AND ("osf_guid"."content_type_id" = 4)) AS "__guids", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_contributor"."id", "osf_contributor"."visible", "osf_contributor"."user_id", "osf_contributor"."node_id", "osf_contributor"."_order", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_osfuser"."id", "osf_osfuser"."password", "osf_osfuser"."last_login", "osf_osfuser"."is_superuser", "osf_osfuser"."created", "osf_osfuser"."modified", "osf_osfuser"."content_type_pk", "osf_osfuser"."spam_status", "osf_osfuser"."spam_pro_tip", "osf_osfuser"."spam_data", "osf_osfuser"."date_last_reported", "osf_osfuser"."reports", "osf_osfuser"."username", "osf_osfuser"."fullname", "osf_osfuser"."is_registered", "osf_osfuser"."security_messages", "osf_osfuser"."is_invited", "osf_osfuser"."unclaimed_records", "osf_osfuser"."contributor_added_email_records", "osf_osfuser"."member_added_email_records", "osf_osfuser"."group_connected_email_records", "osf_osfuser"."merged_by_id", "osf_osfuser"."verification_key", "osf_osfuser"."verification_key_v2", "osf_osfuser"."email_last_sent", "osf_osfuser"."change_password_last_attempt", "osf_osfuser"."old_password_invalid_attempts", "osf_osfuser"."email_verifications", "osf_osfuser"."mailchimp_mailing_lists", "osf_osfuser"."osf_mailing_lists", "osf_osfuser"."date_registered", "osf_osfuser"."given_name", "osf_osfuser"."middle_names", "osf_osfuser"."family_name", "osf_osfuser"."suffix", "osf_osfuser"."external_identity", "osf_osfuser"."jobs", "osf_osfuser"."schools", "osf_osfuser"."social", "osf_osfuser"."date_last_login", "osf_osfuser"."date_confirmed", "osf_osfuser"."date_disabled", "osf_osfuser"."deleted", "osf_osfuser"."comments_viewed_timestamp", "osf_osfuser"."timezone", "osf_osfuser"."locale", "osf_osfuser"."requested_deactivation", "osf_osfuser"."contacted_deactivation", "osf_osfuser"."notifications_configured", "osf_osfuser"."accepted_terms_of_service", "osf_osfuser"."chronos_user_id", "osf_osfuser"."department", "osf_osfuser"."is_active", "osf_osfuser"."is_staff", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_guid"."modified", "osf_guid"."id", "osf_guid"."_id", "osf_guid"."content_type_id", "osf_guid"."object_id", "osf_guid"."created")) AS "__fields" FROM "osf_guid" WHERE ("osf_guid"."object_id" = osf_osfuser."id") AND ("osf_guid"."content_type_id" = 1)))) AS "__fields" FROM "osf_osfuser" WHERE ("osf_osfuser"."id" = osf_contributor."user_id")))) AS "__fields" FROM "osf_contributor" WHERE ("osf_contributor"."node_id" = osf_abstractnode."id")) AS "__contributor" FROM "osf_abstractnode" WHERE "osf_abstractnode"."type" = osf.node'

In [133]: pprint.pprint(Node.objects.include('guids', 'contributor__user__guids').explain())
('Index Scan using osf_abstractnode_type_357dbc68_like on osf_abstractnode  '
 '(cost=0.14..178.33 rows=1 width=2820)\n'
 "  Index Cond: ((type)::text = 'osf.node'::text)\n"
 '  SubPlan 1\n'
 '    ->  Aggregate  (cost=8.17..8.18 rows=1 width=32)\n'
 '          ->  Index Scan using osf_guid_content_type_id_5ed9fdc2 on '
 'osf_guid  (cost=0.14..8.16 rows=1 width=544)\n'
 '                Index Cond: (content_type_id = 4)\n'
 '                Filter: (object_id = osf_abstractnode.id)\n'
 '  SubPlan 4\n'
 '    ->  Aggregate  (cost=161.98..161.99 rows=1 width=32)\n'
 '          ->  Bitmap Heap Scan on osf_contributor  (cost=4.22..14.76 rows=9 '
 'width=17)\n'
 '                Recheck Cond: (node_id = osf_abstractnode.id)\n'
 '                ->  Bitmap Index Scan on osf_contributor_node_id_149229e8  '
 '(cost=0.00..4.22 rows=9 width=0)\n'
 '                      Index Cond: (node_id = osf_abstractnode.id)\n'
 '          SubPlan 3\n'
 '            ->  Aggregate  (cost=16.34..16.35 rows=1 width=32)\n'
 '                  ->  Index Scan using osf_osfuser_pkey on osf_osfuser  '
 '(cost=0.14..8.15 rows=1 width=6067)\n'
 '                        Index Cond: (id = osf_contributor.user_id)\n'
 '                  SubPlan 2\n'
 '                    ->  Aggregate  (cost=8.17..8.18 rows=1 width=32)\n'
 '                          ->  Index Scan using '
 'osf_guid_content_type_id_5ed9fdc2 on osf_guid osf_guid_1  (cost=0.14..8.16 '
 'rows=1 width=544)\n'
 '                                Index Cond: (content_type_id = 1)\n'
 '                                Filter: (object_id = osf_osfuser.id)')

Prefetch Node with contributor guids

In [135]: str(Node.objects.prefetch_related('guids', 'contributor_set__user__guids').query)
Out[135]: 'SELECT "osf_abstractnode"."id", "osf_abstractnode"."created", "osf_abstractnode"."modified", "osf_abstractnode"."content_type_pk", "osf_abstractnode"."spam_status", "osf_abstractnode"."spam_pro_tip", "osf_abstractnode"."spam_data", "osf_abstractnode"."date_last_reported", "osf_abstractnode"."reports", "osf_abstractnode"."last_logged", "osf_abstractnode"."title", "osf_abstractnode"."description", "osf_abstractnode"."category", "osf_abstractnode"."node_license_id", "osf_abstractnode"."registration_responses", "osf_abstractnode"."registration_responses_migrated", "osf_abstractnode"."type", "osf_abstractnode"."child_node_subscriptions", "osf_abstractnode"."creator_id", "osf_abstractnode"."deleted_date", "osf_abstractnode"."deleted", "osf_abstractnode"."file_guid_to_share_uuids", "osf_abstractnode"."forked_date", "osf_abstractnode"."forked_from_id", "osf_abstractnode"."is_fork", "osf_abstractnode"."is_public", "osf_abstractnode"."is_deleted", "osf_abstractnode"."access_requests_enabled", "osf_abstractnode"."custom_citation", "osf_abstractnode"."comment_level", "osf_abstractnode"."root_id", "osf_abstractnode"."article_doi", "osf_abstractnode"."custom_storage_usage_limit_public", "osf_abstractnode"."custom_storage_usage_limit_private", "osf_abstractnode"."piwik_site_id", "osf_abstractnode"."suspended", "osf_abstractnode"."template_node_id", "osf_abstractnode"."wiki_private_uuids", "osf_abstractnode"."keenio_read_key", (SELECT JSON_AGG(JSON_BUILD_ARRAY("osf_guid"."modified", "osf_guid"."id", "osf_guid"."_id", "osf_guid"."content_type_id", "osf_guid"."object_id", "osf_guid"."created")) AS "__fields" FROM "osf_guid" WHERE ("osf_guid"."object_id" = osf_abstractnode."id") AND ("osf_guid"."content_type_id" = 4)) AS "__guids" FROM "osf_abstractnode" WHERE "osf_abstractnode"."type" = osf.node'

In [136]: pprint.pprint(Node.objects.prefetch_related('guids', 'contributor_set__user__guids').explain())
('Index Scan using osf_abstractnode_type_357dbc68_like on osf_abstractnode  '
 '(cost=0.14..16.34 rows=1 width=2788)\n'
 "  Index Cond: ((type)::text = 'osf.node'::text)\n"
 '  SubPlan 1\n'
 '    ->  Aggregate  (cost=8.17..8.18 rows=1 width=32)\n'
 '          ->  Index Scan using osf_guid_content_type_id_5ed9fdc2 on '
 'osf_guid  (cost=0.14..8.16 rows=1 width=544)\n'
 '                Index Cond: (content_type_id = 4)\n'
 '                Filter: (object_id = osf_abstractnode.id)')