explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IytY

Settings
# exclusive inclusive rows x rows loops node
1. 0.313 7.266 ↓ 9.0 9 1

Limit (cost=440.99..463.92 rows=1 width=62) (actual time=5.435..7.266 rows=9 loops=1)

2. 1.653 6.953 ↓ 4.1 29 1

Result (cost=280.46..440.99 rows=7 width=62) (actual time=1.251..6.953 rows=29 loops=1)

3. 0.528 1.240 ↓ 4.1 29 1

Sort (cost=280.46..280.48 rows=7 width=54) (actual time=1.002..1.240 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.470 0.712 ↓ 4.1 29 1

Bitmap Heap Scan on project (cost=43.91..280.37 rows=7 width=54) (actual time=0.270..0.712 rows=29 loops=1)

  • Recheck Cond: (("organizationId" = 3) AND (status = ANY ('{UNDER_REVIEW,DRAFT,PUBLISHED,FROZEN,CANCELLED,CASHEDOUT,DELIVERED,ENDED}'::text[])))
  • Filter: (("deletedAt" IS NULL) 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: 43
  • Heap Blocks: exact=57
5. 0.026 0.242 ↓ 0.0 0 1

BitmapAnd (cost=43.91..43.91 rows=93 width=0) (actual time=0.234..0.242 rows=0 loops=1)

6. 0.038 0.038 ↑ 1.0 167 1

Bitmap Index Scan on idx_16492_project_organization_id (cost=0.00..5.53 rows=167 width=0) (actual time=0.031..0.038 rows=167 loops=1)

  • Index Cond: ("organizationId" = 3)
7. 0.178 0.178 ↑ 1.0 1,585 1

Bitmap Index Scan on idx_16492_project_status (cost=0.00..38.12 rows=1,585 width=0) (actual time=0.170..0.178 rows=1,585 loops=1)

  • Index Cond: (status = ANY ('{UNDER_REVIEW,DRAFT,PUBLISHED,FROZEN,CANCELLED,CASHEDOUT,DELIVERED,ENDED}'::text[]))
8.          

SubPlan (for Result)

9. 1.218 2.291 ↑ 1.0 1 29

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

10. 1.073 1.073 ↑ 1.0 2 29

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

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

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

12. 0.493 1.015 ↓ 0.0 0 29

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

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

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

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