From what I can see, the index is not entirely loaded up in memory, and that's what causing it to be slow? Am I correct? obs: I changed the "Output" section a little bit for privacy concerns Answer The index scan is fast (1.774 milliseconds on average). The execution is so slow because the index scan is executed 46257 times.

Why is this Index Scan so slow?

From what I can see, the index is not entirely loaded up in memory, and that’s what causing it to be slow? Am I correct?

obs: I changed the “Output” section a little bit for privacy concerns

->  Index Scan using idx_customers_orders on public.customers  (cost=0.57..20.28 rows=1 width=13) (actual time=1.757..1.774 rows=1 loops=46257)
                           Output: customers.id, customers.created_at, customers.created_by ,customers.registration ,customers.name ,customers.description ,customers.address ,customers.location ,customers.code
                           Index Cond: (customers.i_order = orders.i_order)
                           Filter: ((NOT customers.deleted) AND (customers.i_databases = (current_setting('db.tenant'::text))::integer))
                           Rows Removed by Filter: 0
                           Buffers: shared hit=164654 read=68033
                           I/O Timings: read=80791.000

​x
 
->  Index Scan using idx_customers_orders on public.customers  (cost=0.57..20.28 rows=1 width=13) (actual time=1.757..1.774 rows=1 loops=46257)                           Output: customers.id, customers.created_at, customers.created_by ,customers.registration ,customers.name ,customers.description ,customers.address ,customers.location ,customers.code                           Index Cond: (customers.i_order = orders.i_order)                           Filter: ((NOT customers.deleted) AND (customers.i_databases = (current_setting('db.tenant'::text))::integer))                           Rows Removed by Filter: 0                           Buffers: shared hit=164654 read=68033                           I/O Timings: read=80791.000​

Answer

The index scan is fast (1.774 milliseconds on average). The execution is so slow because the index scan is executed 46257 times. Slightly over 1 millisecond for reading an 8kB-block from disk isn’t so bad.

Most likely, the index scan is on the inner side of a nested loop join. Perhaps a different join strategy would be better; since you don’t show the execution plan, we cannot tell.

Advertisement

Answer