explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XdvY

Settings
# exclusive inclusive rows x rows loops node
1. 0.363 6.896 ↓ 9.0 9 1

Limit (cost=301.15..324.08 rows=1 width=62) (actual time=5.138..6.896 rows=9 loops=1)

2. 1.526 6.533 ↓ 4.1 29 1

Result (cost=140.63..301.15 rows=7 width=62) (actual time=1.119..6.533 rows=29 loops=1)

3. 0.588 1.179 ↓ 4.1 29 1

Sort (cost=140.63..140.64 rows=7 width=54) (actual time=0.945..1.179 rows=29 loops=1)

  • Sort Key: (CASE WHEN (project.status = 'UNDER_REVIEW'::text) THEN '1'::numeric WHEN (project.status = 'DRAFT'::text) THEN '2'::numeric WHEN ((project."fundingEndedAt" IS NOT NULL) AND (((project."fundingEndedAt")::date - '2020-09-07'::date) < 10) AND (project.status = 'PUBLISHED'::text)) THEN '3'::numeric WHEN (project.status = 'PUBLISHED'::text) THEN CASE WHEN (('2020-09-07'::date - (project."publishedAt")::date) < 15) THEN 3.5 ELSE '4'::numeric END WHEN (project.status = 'FROZEN'::text) THEN 4.5 WHEN (project.status = 'CASHEDOUT'::text) THEN '5'::numeric WHEN (project.status = 'DELIVERED'::text) THEN '6'::numeric WHEN (project.status = 'ENDED'::text) THEN '7'::numeric WHEN (project.status = 'CANCELLED'::text) THEN '8'::numeric ELSE '9'::numeric END), project."updatedAt" DESC
  • Sort Method: quicksort Memory: 27kB
4. 0.526 0.591 ↓ 4.1 29 1

Bitmap Heap Scan on project (cost=74.66..140.53 rows=7 width=54) (actual time=0.099..0.591 rows=29 loops=1)

  • Recheck Cond: ((("organizationId" = 3) AND ("organizationId" = 3)) OR (("organizationId" = 3) AND ("deletedAt" IS NULL) AND (status = 'TEMPLATE'::text) AND (status = ANY ('{UNDER_REVIEW,DRAFT,PUBLISHED,FROZEN,CANCELLED,CASHEDOUT,DELIVERED,ENDED}'::text[]))))
  • Filter: (("deletedAt" IS NULL) AND (status = ANY ('{UNDER_REVIEW,DRAFT,PUBLISHED,FROZEN,CANCELLED,CASHEDOUT,DELIVERED,ENDED}'::text[])) AND (("authorId" = 80) OR (status = ANY ('{PUBLISHED,FROZEN,CASHEDOUT,DELIVERED,ENDED,TEMPLATE,CANCELLED,UNDER_REVIEW}'::text[])) OR (id = ANY ('{158,280,287,308,399,400,386,43,530,1118,1451,829,1710,51,2420,52,2516,2718}'::bigint[]))))
  • Rows Removed by Filter: 138
  • Heap Blocks: exact=112
5. 0.016 0.065 ↓ 0.0 0 1

BitmapOr (cost=74.66..74.66 rows=19 width=0) (actual time=0.057..0.065 rows=0 loops=1)

6. 0.032 0.032 ↓ 16.7 167 1

Bitmap Index Scan on idx_16492_project_organization_id (cost=0.00..4.38 rows=10 width=0) (actual time=0.024..0.032 rows=167 loops=1)

  • Index Cond: (("organizationId" = 3) AND ("organizationId" = 3))
7. 0.017 0.017 ↓ 0.0 0 1

Bitmap Index Scan on project_list_test1 (cost=0.00..70.28 rows=9 width=0) (actual time=0.009..0.017 rows=0 loops=1)

  • Index Cond: (("organizationId" = 3) AND ("deletedAt" IS NULL) AND (status = 'TEMPLATE'::text) AND (status = ANY ('{UNDER_REVIEW,DRAFT,PUBLISHED,FROZEN,CANCELLED,CASHEDOUT,DELIVERED,ENDED}'::text[])))
8.          

SubPlan (for Result)

9. 1.247 2.175 ↑ 1.0 1 29

Aggregate (cost=10.28..10.29 rows=1 width=4) (actual time=0.067..0.075 rows=1 loops=29)

10. 0.928 0.928 ↑ 1.0 2 29

Index Scan using project_organizer_existing on "projectOrganizer" (cost=0.28..10.28 rows=2 width=8) (actual time=0.013..0.032 rows=2 loops=29)

  • Index Cond: ("projectId" = project.id)
11. 0.783 1.653 ↑ 1.0 1 29

Aggregate (cost=12.58..12.59 rows=1 width=4) (actual time=0.049..0.057 rows=1 loops=29)

12. 0.493 0.870 ↓ 0.0 0 29

Bitmap Heap Scan on "projectParticipant" (cost=4.33..12.56 rows=7 width=4) (actual time=0.029..0.030 rows=0 loops=29)

  • Recheck Cond: (("projectId" = project.id) AND ("deletedAt" IS NULL))
13. 0.377 0.377 ↓ 0.0 0 29

Bitmap Index Scan on project_participant_existing (cost=0.00..4.33 rows=7 width=0) (actual time=0.013..0.013 rows=0 loops=29)

  • Index Cond: ("projectId" = project.id)
Planning time : 0.647 ms
Execution time : 7.264 ms