Improve organization_recognitions list API

there's pagination, but that's also not going to help, as we're returning too many related fields in that response
For one single object retrieval, we're hitting 15 different queries, that's quite a lot. Out of these 15, there's one query which takes around 5.09 seconds to complete, so for 20 different feeds on single page, it takes around 250 queries
IMO, we must create one light serializer for the list view, as I'm pretty sure we're not showing all the fields in the list view. We return only those fields that we show in the UI while listing. Return more fields to the detail page only if needed.
Try to use, select_related and prefetch_related for the related fields. Also, I noticed that in serializer, we're hitting queries like this Documents.objects.filter(post=post_id), instead, we should use the related query
Queries running for one object when sent to serializer
[{u'sql': u'SELECT DISTINCT "feeds_post"."id", "feeds_post"."created_by_id", "feeds_post"."created_on", "feeds_post"."title", "feeds_post"."description", "feeds_post"."user_id", "feeds_post"."transaction_id", "feeds_post"."nomination_id", "feeds_post"."greeting_id", "feeds_post"."ecard_id", "feeds_post"."gif", "feeds_post"."published_date", "feeds_post"."priority", "feeds_post"."prior_till", "feeds_post"."shared_with", "feeds_post"."post_type", "feeds_post"."active_days", "feeds_post"."modified_by_id", "feeds_post"."modified_on", "feeds_post"."mark_delete" FROM "feeds_post" LEFT OUTER JOIN "feeds_post_organizations" ON ( "feeds_post"."id" = "feeds_post_organizations"."post_id" ) LEFT OUTER JOIN "feeds_post_departments" ON ( "feeds_post"."id" = "feeds_post_departments"."post_id" ) INNER JOIN "profiles_customuser" ON ( "feeds_post"."created_by_id" = "profiles_customuser"."id" ) LEFT OUTER JOIN "profiles_department_users" ON ( "profiles_customuser"."id" = "profiles_department_users"."customuser_id" ) LEFT OUTER JOIN "feeds_post_job_families" ON ( "feeds_post"."id" = "feeds_post_job_families"."post_id" ) LEFT OUTER JOIN "profiles_customuser" T12 ON ( "feeds_post"."user_id" = T12."id" ) LEFT OUTER JOIN "events_repeatedevent" ON ( "feeds_post"."greeting_id" = "events_repeatedevent"."id" ) WHERE ((("feeds_post"."mark_delete" = false AND ("feeds_post_organizations"."organization_id" IN (426) OR "feeds_post_departments"."department_id" IN (SELECT U0."id" FROM "profiles_department" U0 INNER JOIN "profiles_department_users" U1 ON ( U0."id" = U1."department_id" ) WHERE (U0."is_user_created" = false AND U1."customuser_id" = 762184)) OR ("profiles_customuser"."organization_id" IN (426) AND "feeds_post"."shared_with" = 20) OR ("feeds_post"."shared_with" = 10 AND "profiles_department_users"."department_id" IN (SELECT U0."id" FROM "profiles_department" U0 INNER JOIN "profiles_department_users" U1 ON ( U0."id" = U1."department_id" ) WHERE (U0."is_user_created" = false AND U1."customuser_id" = 762184))) OR ("feeds_post_job_families"."userjobfamily_id" IN (82) AND "feeds_post"."shared_with" = 40))) OR ("feeds_post"."mark_delete" = false AND "feeds_post"."created_by_id" = 762184)) AND NOT ("feeds_post"."post_type" = 8) AND ("feeds_post"."post_type" = 1 OR (T12."is_dob_public" = true AND "events_repeatedevent"."event_type" = 81 AND "feeds_post"."post_type" = 9 AND "feeds_post"."title" = \'greeting_post\') OR ("events_repeatedevent"."event_type" = 80 AND T12."is_anniversary_public" = true AND "feeds_post"."post_type" = 9 AND "feeds_post"."title" = \'greeting_post\') OR "feeds_post"."post_type" = 2)) ORDER BY "feeds_post"."id" DESC LIMIT 1',
  u'time': u'5.097'},
 {u'sql': u'SELECT "profiles_organization"."id", "profiles_organization"."img", "profiles_organization"."img_display", "profiles_organization"."img_thumbnail", "profiles_organization"."name", "profiles_organization"."slug", "profiles_organization"."domain", "profiles_organization"."background_color", "profiles_organization"."country", "profiles_organization"."timezone", "profiles_organization"."region", "profiles_organization"."business_unit", "profiles_organization"."emergency_contact_id", "profiles_organization"."employee_referral_email", "profiles_organization"."business_referral_email", "profiles_organization"."parent_org_id", "profiles_organization"."user_welcome_email_id", "profiles_organization"."address", "profiles_organization"."delivery_address_id", "profiles_organization"."register_profile_facecams", "profiles_organization"."create_chat_group", "profiles_organization"."chat_enabled", "profiles_organization"."has_secondary_logo", "profiles_organization"."p2p_email_template_id", "profiles_organization"."web_session_expiry", "profiles_organization"."reward_redemption_template_id", "profiles_organization"."appreciation_last_reset_date", "profiles_organization"."sample_data_file", "profiles_organization"."signup_option", "profiles_organization"."upload_benefit_receipt", "profiles_organization"."organization_status", "profiles_organization"."source_id", "profiles_organization"."source_origin", "profiles_organization"."currency", "profiles_organization"."enable_biometrics", "profiles_organization"."is_boid_client_org", "profiles_organization"."api_credentials_for_client" FROM "profiles_organization" INNER JOIN "feeds_post_organizations" ON ( "profiles_organization"."id" = "feeds_post_organizations"."organization_id" ) WHERE "feeds_post_organizations"."post_id" = 411692 ORDER BY "profiles_organization"."name" ASC',
  u'time': u'0.002'},
 {u'sql': u'SELECT "profiles_customuser"."id", "profiles_customuser"."password", "profiles_customuser"."last_login", "profiles_customuser"."email", "profiles_customuser"."first_name", "profiles_customuser"."last_name", "profiles_customuser"."created", "profiles_customuser"."updated", "profiles_customuser"."is_active", "profiles_customuser"."is_staff", "profiles_customuser"."is_admin_staff", "profiles_customuser"."is_marketing_staff", "profiles_customuser"."is_send_low_balance", "profiles_customuser"."is_send_affilated_org_low_balance", "profiles_customuser"."is_superuser", "profiles_customuser"."is_management_staff", "profiles_customuser"."img", "profiles_customuser"."official_title", "profiles_customuser"."real_title", "profiles_customuser"."phone_number", "profiles_customuser"."office_phone_number", "profiles_customuser"."address", "profiles_customuser"."address2", "profiles_customuser"."delivery_address_id", "profiles_customuser"."martial_status", "profiles_customuser"."date_of_birth", "profiles_customuser"."wedding_date", "profiles_customuser"."children", "profiles_customuser"."mantra", "profiles_customuser"."is_profile_public", "profiles_customuser"."is_dob_public", "profiles_customuser"."organization_id", "profiles_customuser"."is_chat_enabled", "profiles_customuser"."employee_id", "profiles_customuser"."is_p2p_staff", "profiles_customuser"."hide_appreciation", "profiles_customuser"."is_inventory_user", "profiles_customuser"."probation_end_date", "profiles_customuser"."contract_start_date", "profiles_customuser"."contract_expiry_date", "profiles_customuser"."termination_date", "profiles_customuser"."employee_type", "profiles_customuser"."lan_id", "profiles_customuser"."is_email_otp_enabled", "profiles_customuser"."gender", "profiles_customuser"."nationality", "profiles_customuser"."id_type", "profiles_customuser"."id_number", "profiles_customuser"."salary", "profiles_customuser"."benefit_grade_id", "profiles_customuser"."base_salary", "profiles_customuser"."line_manager_id", "profiles_customuser"."key", "profiles_customuser"."is_dm_admin", "profiles_customuser"."is_financial_consultant", "profiles_customuser"."website_url", "profiles_customuser"."is_department_receipt_approver", "profiles_customuser"."is_point_allocation_approver", "profiles_customuser"."is_maker", "profiles_customuser"."is_checker", "profiles_customuser"."is_benefit_receipt_approver", "profiles_customuser"."has_accepted_terms_and_cond", "profiles_customuser"."show_dependent_details", "profiles_customuser"."status", "profiles_customuser"."created_by_id", "profiles_customuser"."is_anniversary_public", "profiles_customuser"."tax_percentage", "profiles_customuser"."display_id" FROM "profiles_customuser" WHERE "profiles_customuser"."id" = 759892',
  u'time': u'0.001'},
 {u'sql': u'SELECT "profiles_customuser"."id", "profiles_customuser"."password", "profiles_customuser"."last_login", "profiles_customuser"."email", "profiles_customuser"."first_name", "profiles_customuser"."last_name", "profiles_customuser"."created", "profiles_customuser"."updated", "profiles_customuser"."is_active", "profiles_customuser"."is_staff", "profiles_customuser"."is_admin_staff", "profiles_customuser"."is_marketing_staff", "profiles_customuser"."is_send_low_balance", "profiles_customuser"."is_send_affilated_org_low_balance", "profiles_customuser"."is_superuser", "profiles_customuser"."is_management_staff", "profiles_customuser"."img", "profiles_customuser"."official_title", "profiles_customuser"."real_title", "profiles_customuser"."phone_number", "profiles_customuser"."office_phone_number", "profiles_customuser"."address", "profiles_customuser"."address2", "profiles_customuser"."delivery_address_id", "profiles_customuser"."martial_status", "profiles_customuser"."date_of_birth", "profiles_customuser"."wedding_date", "profiles_customuser"."children", "profiles_customuser"."mantra", "profiles_customuser"."is_profile_public", "profiles_customuser"."is_dob_public", "profiles_customuser"."organization_id", "profiles_customuser"."is_chat_enabled", "profiles_customuser"."employee_id", "profiles_customuser"."is_p2p_staff", "profiles_customuser"."hide_appreciation", "profiles_customuser"."is_inventory_user", "profiles_customuser"."probation_end_date", "profiles_customuser"."contract_start_date", "profiles_customuser"."contract_expiry_date", "profiles_customuser"."termination_date", "profiles_customuser"."employee_type", "profiles_customuser"."lan_id", "profiles_customuser"."is_email_otp_enabled", "profiles_customuser"."gender", "profiles_customuser"."nationality", "profiles_customuser"."id_type", "profiles_customuser"."id_number", "profiles_customuser"."salary", "profiles_customuser"."benefit_grade_id", "profiles_customuser"."base_salary", "profiles_customuser"."line_manager_id", "profiles_customuser"."key", "profiles_customuser"."is_dm_admin", "profiles_customuser"."is_financial_consultant", "profiles_customuser"."website_url", "profiles_customuser"."is_department_receipt_approver", "profiles_customuser"."is_point_allocation_approver", "profiles_customuser"."is_maker", "profiles_customuser"."is_checker", "profiles_customuser"."is_benefit_receipt_approver", "profiles_customuser"."has_accepted_terms_and_cond", "profiles_customuser"."show_dependent_details", "profiles_customuser"."status", "profiles_customuser"."created_by_id", "profiles_customuser"."is_anniversary_public", "profiles_customuser"."tax_percentage", "profiles_customuser"."display_id" FROM "profiles_customuser" WHERE "profiles_customuser"."id" = 759892 ORDER BY "profiles_customuser"."id" ASC LIMIT 1',
  u'time': u'0.002'},
 {u'sql': u'SELECT "profiles_department"."id", "profiles_department"."organization_id", "profiles_department"."name", "profiles_department"."slug", "profiles_department"."is_user_created", "profiles_department"."is_active", "profiles_department"."source_id", "profiles_department"."source_origin" FROM "profiles_department" INNER JOIN "profiles_department_users" ON ( "profiles_department"."id" = "profiles_department_users"."department_id" ) WHERE ("profiles_department"."is_user_created" = false AND "profiles_department_users"."customuser_id" = 759892) ORDER BY "profiles_department"."name" ASC, "profiles_department"."slug" ASC, "profiles_department"."id" ASC',
  u'time': u'0.003'},
 {u'sql': u'SELECT (1) AS "a" FROM "feeds_postliked" WHERE ("feeds_postliked"."post_id" = 411692 AND "feeds_postliked"."created_by_id" = 762184) LIMIT 1',
  u'time': u'0.001'},
 {u'sql': u'SELECT COUNT(*) AS "__count" FROM "feeds_postliked" WHERE "feeds_postliked"."post_id" = 411692',
  u'time': u'0.001'},
 {u'sql': u'SELECT COUNT(*) AS "__count" FROM "feeds_comment" WHERE ("feeds_comment"."post_id" = 411692 AND "feeds_comment"."mark_delete" = false)',
  u'time': u'0.001'},
 {u'sql': u'SELECT (1) AS "a" FROM "feeds_postliked" WHERE "feeds_postliked"."post_id" = 411692 LIMIT 1',
  u'time': u'0.001'},
 {u'sql': u'SELECT "finance_transaction"."id", "finance_transaction"."user_id", "finance_transaction"."creator_id", "finance_transaction"."organization_id", "finance_transaction"."reason_id", "finance_transaction"."points", "finance_transaction"."value", "finance_transaction"."context", "finance_transaction"."benefit_selection_round_id", "finance_transaction"."updated", "finance_transaction"."created", "finance_transaction"."is_manually_created", "finance_transaction"."message", "finance_transaction"."remark", "finance_transaction"."expiry_date", "finance_transaction"."status", "finance_transaction"."bulk_allocation_id", "finance_transaction"."updated_by_id", "finance_transaction"."department_id", "finance_transaction"."payment_charge_id" FROM "finance_transaction" INNER JOIN "feeds_post_transactions" ON ( "finance_transaction"."id" = "feeds_post_transactions"."transaction_id" ) WHERE "feeds_post_transactions"."post_id" = 411692 ORDER BY "finance_transaction"."created" DESC, "finance_transaction"."id" DESC LIMIT 1',
  u'time': u'0.001'},
 {u'sql': u'SELECT (1) AS "a" FROM "feeds_postliked" WHERE ("feeds_postliked"."post_id" = 411692 AND "feeds_postliked"."created_by_id" = 762184) LIMIT 1',
  u'time': u'0.001'},
 {u'sql': u'SELECT "finance_transaction"."id", "finance_transaction"."user_id", "finance_transaction"."creator_id", "finance_transaction"."organization_id", "finance_transaction"."reason_id", "finance_transaction"."points", "finance_transaction"."value", "finance_transaction"."context", "finance_transaction"."benefit_selection_round_id", "finance_transaction"."updated", "finance_transaction"."created", "finance_transaction"."is_manually_created", "finance_transaction"."message", "finance_transaction"."remark", "finance_transaction"."expiry_date", "finance_transaction"."status", "finance_transaction"."bulk_allocation_id", "finance_transaction"."updated_by_id", "finance_transaction"."department_id", "finance_transaction"."payment_charge_id" FROM "finance_transaction" INNER JOIN "feeds_post_transactions" ON ( "finance_transaction"."id" = "feeds_post_transactions"."transaction_id" ) WHERE "feeds_post_transactions"."post_id" = 411692 ORDER BY "finance_transaction"."created" DESC, "finance_transaction"."id" DESC LIMIT 1',
  u'time': u'0.001'},
 {u'sql': u'SELECT "feeds_images"."id", "feeds_images"."image", "feeds_images"."img_large", "feeds_images"."img_display", "feeds_images"."img_thumbnail", "feeds_images"."post_id", "feeds_images"."comment_id" FROM "feeds_images" WHERE "feeds_images"."post_id" = 411692',
  u'time': u'0.001'},
 {u'sql': u'SELECT "profiles_department"."id", "profiles_department"."organization_id", "profiles_department"."name", "profiles_department"."slug", "profiles_department"."is_user_created", "profiles_department"."is_active", "profiles_department"."source_id", "profiles_department"."source_origin" FROM "profiles_department" INNER JOIN "feeds_post_departments" ON ( "profiles_department"."id" = "feeds_post_departments"."department_id" ) WHERE ("profiles_department"."is_user_created" = false AND "feeds_post_departments"."post_id" = 411692) ORDER BY "profiles_department"."name" ASC, "profiles_department"."slug" ASC, "profiles_department"."id" ASC',
  u'time': u'0.002'},
 {u'sql': u'SELECT "profiles_userjobfamily"."id" FROM "profiles_userjobfamily" INNER JOIN "feeds_post_job_families" ON ( "profiles_userjobfamily"."id" = "feeds_post_job_families"."userjobfamily_id" ) WHERE "feeds_post_job_families"."post_id" = 411692 LIMIT 21',
  u'time': u'0.001'}]
The idea here is to create a separate serializer for list view and detail view; check with the mobile team what fields they're showing in the list page, and we will send only those fields. We will also use select_related and prefetch_related for fetching related models. Also, ensure to avoid queries like Documents.objects.filter(post=post_id) in the serializer's get_field method, instead, retrieve using a relation that is prefetched to avoid an N + 1 query. Please create a fresh serializer for this; do not inherit it from previous serializers.
rewardz / Feeds

Improve organization_recognitions list API #215
