Re: Slow index scan - Pgsql 9.2

On 10 January 2017 at 14:06, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
> -> Index Scan using "clientid_customers" on "customers" "c" (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728 rows=2513 loops=1)
> Index Cond: ("clientid" = "qp"."client_id")
> Filter: (NOT "deleted")
> Rows Removed by Filter: 1068
> Total runtime: 4075.753 ms
>
> Why a search for "client_id" is so slow??

EXPLAIN (ANALYZE, BUFFERS) might reveal something.

Perhaps each of the 2513 found rows, plus the 1068 filtered out rows
were spread over the table. Perhaps each on their own heap page, and
all those pages had to be read from disk. The BUFFERS option might
help show if this is the case.

Does it execute as slowly when you run it for a 2nd time?

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services