Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2zbB-000nPf-20 for pgsql-performance@arkaria.postgresql.org; Wed, 18 Mar 2026 22:42:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1w2zbA-00F4Np-1W for pgsql-performance@arkaria.postgresql.org; Wed, 18 Mar 2026 22:42:44 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1w2zb9-00F4Ng-2z for pgsql-performance@lists.postgresql.org; Wed, 18 Mar 2026 22:42:44 +0000 Received: from mail-qv1-xf2f.google.com ([2607:f8b0:4864:20::f2f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1w2zb5-00000000RGA-498P for pgsql-performance@lists.postgresql.org; Wed, 18 Mar 2026 22:42:43 +0000 Received: by mail-qv1-xf2f.google.com with SMTP id 6a1803df08f44-89c43c4c383so4973216d6.1 for ; Wed, 18 Mar 2026 15:42:41 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1773873760; cv=none; d=google.com; s=arc-20240605; b=lvYojZ/BSLgPoE1odZhro+d7qYb8vBmy0B6ZNP12D2hhkolbZ9XU5r9VELt6RGPma/ DaYiJSlOAorxutss/TVdQLEV433GXnPmuLzpamESzQrewg5SpXv56Fo4gP9sQ/9zV/3/ hHLYEp6ZMb4nHsw6TW0kzDLePqmJFIQw+BDkm803czZUlgywCw6lmwRfwEIyOTZjFoNV qTpj/WtvYginwVykzhnHr9PUNzVViwqSM/JgBukPH4KMoxQyAWx87iwb2lqejhgh2RO4 YZBhVkCpp/m0fCP0E8LpoRn2PcvjP+XRXVAfPvVS79Px044ooXu8nXUNSEyO0M5uMVAk rmwA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=OI47xKu5epEfI4inVvGmveUKseDV/GasVWD20sAqBfs=; fh=ZtHO97arb/PLl4onJSLpAUXHMu/bxk9Uia886ea9yfE=; b=Ge2bxDS6CamVYwdJ3K57iIbUFeHIxdS7XzQ1lo5A31JHQomdiNDIliUxmMMGC3JRtx sT5nmr2G+37QL5WmjnEZTK0p7f4c6wglO3TVAIIj6tsE7QFGcZAh+KqrN3GRqmlPbcSQ P8Qhz/vUe284gO2hDEzcgZwpMZVmZP4WnjmsBIB8F09gDvh8766+Jto/Ck4Z9UGktzC7 /G6mKYGMCwMOwtO+WIMg4eB4dNiwSJJHNr5znogwmnTeTdKJskPppRwR5VfmGrqPOmep Q5ft4s4dUl0BPYPZI2gvuO5RG6UWee9lKYsgNgloNYiepC8EoDMKNzLU2WOk3t7yTRxu JTgQ==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1773873760; x=1774478560; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=OI47xKu5epEfI4inVvGmveUKseDV/GasVWD20sAqBfs=; b=LJit3IHeAVIG0gADvgxkQlDGpkdzoe8KGG+i8zb2rhL+hK+ufrLwg7hQdVKtAtd9UR /iD86jZdnu3cnZ1kT9QDuPJf91QVfbVYZ3O1/un9jGu7hRIaWnLQol248QK5Q9f+7Ojt r/rzFuP3TW3XSlyPx4lEIQ8D3tvZeQSGk9741rV0thx+1ZwrfGvQ8LeyycobQIb2aDyN A8+k63wFnm4mUVdXF5y4nYnVZlmmQaJYqw93FqpkRCrZF/jdMWApXRujRs8mH03u6mjW UCAh4FVbQXHCiPnX+XNqkgwpjncbEp6LV4qrqBM+PWiBD8CkTXseQ48yvHbg3hC4ViFi SYvw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1773873760; x=1774478560; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=OI47xKu5epEfI4inVvGmveUKseDV/GasVWD20sAqBfs=; b=UuXjEup6NxdNu2p7uA0iT+GfLc7/DWUvbY8iiiMbPEQZEKiwmrjLok752A0bP9vGAy fQvvtteDGB2g/kK83qypCuk1W1eRsZFDoYCLzPlx7iuapxjENRtKI95uNoLL58sXip6c tKB5CgfXi8L8zkxiVY6bgmsXrK3VdhYqyg8ktrH3ch+0caOHryaKWwJFnllEidhlZfFk 3CiHT/HO4FnOiSHuAx86+EPhkp7kJmIRFOYW6GZEcs4izd9i6E03YDZIYiqcbUc8HUM1 U3WWCxweXsII1ove/q9DKxn4BUw5YmxDNs9ZlyHFddSGUEYkNu+WcBfJ6uIQKsXCBUgU XkfQ== X-Forwarded-Encrypted: i=1; AJvYcCWZ1zY9hA2J+RTBbVtH6S/lgZ/XV7voGFIcUYNPZ/IIFNaRqmsJJd0D8uG2m4MXJO2ugOH7CsN76cMqOIMH/2fqHA==@lists.postgresql.org X-Gm-Message-State: AOJu0Yw9C2nmCTWqqOx2kVisisG2ogk7Kvmiw3jf2Bt+QAKzEo+nq4S5 bDTowxjdYD21bpJY6Kfni4cNGgwOP35ksUU2O4XQOcpdNtHw/JlMkI2f5g4iVxYqpFvNmztwVnS 6PYZiR2BzcEOsrWzFhCIMuCfd5X0pNedYT/cz X-Gm-Gg: ATEYQzxq/0mxOvz2doKS+azz5MYswTDrxhURcyrd/agYW4stko3LnrAo7pVPW8W0/u8 GOEvCePDqCGSg9b0HZa8/H+4GzDH9vVzPe1T+sneG5JbHmR0aLSMc6soiC11F78pg6HYfHEgWyW pwGW7gncef+1xmEHvOA5+WKo2yoTy1JK3SouWrDeZmzcKTE86Gd+kjCqdl1tTS7GF/+WhkMzmZ1 AH3WNFNWe/sTs9bBS+HW85VErXWz+bGaahbYJ3sGFp9tcc2rTajWgGTrzWToNM5bJoGMMobiLBI Kud7+tZyGshxZvpkl1r3dgnNad4IF/NIG7V+iQPoCihkX46aRAYYg8vqH8bkzAt4+ZObEwE6oQ= = X-Received: by 2002:a05:6214:5d8d:b0:89c:6a17:3d5b with SMTP id 6a1803df08f44-89c6b4b79b5mr82898016d6.18.1773873760020; Wed, 18 Mar 2026 15:42:40 -0700 (PDT) MIME-Version: 1.0 References: <574349.1773786268@sss.pgh.pa.us> <381e7d47-8021-458d-9a64-0cdb80b004f0@gmail.com> <37499019-dab7-4a6b-933a-d78f88d2664c@gmail.com> In-Reply-To: <37499019-dab7-4a6b-933a-d78f88d2664c@gmail.com> From: Merlin Moncure Date: Wed, 18 Mar 2026 16:42:29 -0600 X-Gm-Features: AaiRm50ximNOtpqEtXUambfGdp57vTV8IdQtHoGAaYtcS5R1zJ3x6OrFkOOjuJ8 Message-ID: Subject: Re: postgres chooses objectively wrong index To: Alexey Ermakov Cc: Tom Lane , pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000057ad44064d542b5f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000057ad44064d542b5f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Mar 18, 2026 at 2:12=E2=80=AFPM Alexey Ermakov wrote: > On 2026-03-19 00:38, Merlin Moncure wrote: > > Interesting... In that case functional index should help (as it also make= s >> statistic for the planner): >> >> create index concurrently on task_task_execution_state_idx async.task us= ing btree ((async.task_execution_state(task))); >> >> analyze async.task; >> >> > This can't help performance, as the index... > CREATE INDEX ON async.task(concurrency_pool, priority, entered) > WHERE async.task_execution_state(task) =3D 'READY'; > > ...is very precisely configured to provide exactly what's needed; I need > tasks for that exact pool in that exact order if and only if ready. The > partial predicate is designed to keep the index nice and small since only= a > small portion of tasks would be eligible at any specific time. > > The index I suggested was not intended to be used by such queries, it's > only a way to provide statistics for the planner as `create statistics` o= n > expression is not working here. > > It might not be enough and require additional columns (in that case it > will be replacement for your index) or perhaps elevated statistics target= . > It could even make things worse but I'm sure you have ways to test that > before putting it on an important database. > > It should help if planner underestimate number of rows but even then tota= l > estimation won't be perfect when we have 2 conditions on state that > obviously statistically dependent. Combining both conditions on applicati= on > side would work much better if that is possible... > > What would also might help there - output of `explain (analyze, buffers)` > of a query that really had a bad plan and executes in seconds with sizes = of > indexes. And same output but with `set enable_sort =3D off` to see plan t= hat > supposed to be better. And just in case number of live/dead tuples in tha= t > table from pg_stat_user_tables. > Roger. Turns out, I don't have access to pg_statistic (working on that). Here are the plans with buffers: orchestrator_service_user@orchestrator=3D> explain (analyze, buffers) selec= t > * from async.task where async.task_execution_state(task.*) =3D > 'READY'::async.task_execution_state_t and concurrency_pool =3D > '065.laqjjj_live' order by priority, entered limit 10; > > QUERY PLAN > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------ > Limit (cost=3D25.88..25.90 rows=3D10 width=3D563) (actual time=3D35.024= ..35.026 > rows=3D0 loops=3D1) > Buffers: shared hit=3D12542 > -> Sort (cost=3D25.88..26.33 rows=3D179 width=3D563) (actual > time=3D35.023..35.024 rows=3D0 loops=3D1) > Sort Key: priority, entered > Sort Method: quicksort Memory: 25kB > Buffers: shared hit=3D12542 > -> Bitmap Heap Scan on task (cost=3D9.23..22.01 rows=3D179 > width=3D563) (actual time=3D34.989..34.990 rows=3D0 loops=3D1) > Recheck Cond: ((async.task_execution_state(task.*) =3D ANY > ('{READY,RUNNING,YIELDED}'::async.task_execution_state_t[])) AND > (concurrency_pool =3D '065.laqjjj_live'::text) AND > (async.task_execution_state(task.* > Buffers: shared hit=3D12536 > -> BitmapAnd (cost=3D9.23..9.23 rows=3D3 width=3D0) (act= ual > time=3D34.979..34.980 rows=3D0 loops=3D1) > Buffers: shared hit=3D12536 > -> Bitmap Index Scan on task_task_id_idx > (cost=3D0.00..4.38 rows=3D575191 width=3D0) (actual time=3D34.882..34.88= 3 rows=3D97 > loops=3D1) > Buffers: shared hit=3D12502 > -> Bitmap Index Scan on > task_concurrency_pool_priority_entered_idx (cost=3D0.00..4.51 rows=3D179 > width=3D0) (actual time=3D0.092..0.093 rows=3D0 loops=3D1) > Index Cond: (concurrency_pool =3D > '065.laqjjj_live'::text) > Buffers: shared hit=3D34 > Planning: > Buffers: shared hit=3D350 > Planning Time: 1.571 ms > Execution Time: 35.091 ms > (20 rows) orchestrator_service_user@orchestrator=3D> set enable_sort to false; > SET > > orchestrator_service_user@orchestrator=3D> explain (analyze, buffers) > select * from async.task where async.task_execution_state(task.*) =3D > 'READY'::async.task_execution_state_t and concurrency_pool =3D > '065.laqjjj_live' order by priority, entered limit 10; > > QUERY PLAN > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------- > Limit (cost=3D0.50..39.87 rows=3D10 width=3D563) (actual time=3D0.091..= 0.092 > rows=3D0 loops=3D1) > Buffers: shared hit=3D34 > -> Index Scan using task_concurrency_pool_priority_entered_idx on tas= k > (cost=3D0.50..705.21 rows=3D179 width=3D563) (actual time=3D0.090..0.091= rows=3D0 > loops=3D1) > Index Cond: (concurrency_pool =3D '065.laqjjj_live'::text) > Buffers: shared hit=3D34 > Planning Time: 0.251 ms > Execution Time: 0.110 ms > (7 rows) What I'm driving at here is that while statistics contribute to the issue, it's somewhat baffling that postgres estimates the 'good' plan slower than the 'bad' plan. Note that it incorrectly estimated 500k rows in the heap scan to avoid 179 heap fetches, since scanning the index itself is a wash, with a non-trivial recheck. So I see this as a planning problem because, even with the estimated stats the plan makes no sense. What seems to have suppressed the bad plan is: REINDEX INDEX async.task_task_id_idx; yielding this plan: orchestrator_service_user@orchestrator=3D> explain (analyze, buffers) selec= t * from async.task where async.task_execution_state(task.*) =3D 'READY'::async.task_execution_state_t and concurrency_pool =3D '065.laqjjj_live' order by priority, entered limit 10; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------- Limit (cost=3D0.50..39.88 rows=3D10 width=3D563) (actual time=3D0.111..0.= 112 rows=3D0 loops=3D1) Buffers: shared hit=3D34 -> Index Scan using task_concurrency_pool_priority_entered_idx on task (cost=3D0.50..685.64 rows=3D174 width=3D563) (actual time=3D0.110..0.110 r= ows=3D0 loops=3D1) Index Cond: (concurrency_pool =3D '065.laqjjj_live'::text) Buffers: shared hit=3D34 Planning: Buffers: shared hit=3D311 read=3D41 I/O Timings: shared read=3D23.052 Planning Time: 24.972 ms Execution Time: 0.152 ms ...with no planner tweaks. forcing the bad plan, orchestrator_service_user@orchestrator=3D> set enable_indexscan to false; SET Time: 98.377 ms orchestrator_service_user@orchestrator=3D> explain (analyze, buffers) selec= t * from async.task where async.task_execution_state(task.*) =3D 'READY'::async.task_execution_state_t and concurrency_pool =3D '065.laqjjj_live' order by priority, entered limit 10; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------- Limit (cost=3D745.70..745.73 rows=3D10 width=3D563) (actual time=3D0.103.= .0.103 rows=3D0 loops=3D1) Buffers: shared hit=3D40 -> Sort (cost=3D745.70..746.14 rows=3D174 width=3D563) (actual time=3D0.102..0.102 rows=3D0 loops=3D1) Sort Key: priority, entered Sort Method: quicksort Memory: 25kB Buffers: shared hit=3D40 -> Bitmap Heap Scan on task (cost=3D4.55..741.94 rows=3D174 width=3D563) (actual time=3D0.068..0.069 rows=3D0 loops=3D1) Recheck Cond: ((concurrency_pool =3D '065.laqjjj_live'::text= ) AND (async.task_execution_state(task.*) =3D 'READY'::async.task_execution_state_t)) Buffers: shared hit=3D34 -> Bitmap Index Scan on task_concurrency_pool_priority_entered_idx (cost=3D0.00..4.51 rows=3D174 width=3D0) (actual time=3D0.064..0.064 rows=3D0 loops=3D1) Index Cond: (concurrency_pool =3D '065.laqjjj_live'::text) Buffers: shared hit=3D34 Planning: Buffers: shared hit=3D1 Planning Time: 0.258 ms Execution Time: 0.136 ms Point being, bloat is definitely a factor here. However, the database seems to be doing the opposite of what's expected, in the presence of bloat, it's veering toward a more bloat sensitive plan. merlin --00000000000057ad44064d542b5f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Mar 18, 2026 at 2:12=E2=80=AFPM A= lexey Ermakov <alexius.work@gm= ail.com> wrote:
=20 =20 =20
On 2026-03-19 00:38, Merlin Moncure wrote:
=20

Interesting... In that case functional= index should help (as it also makes statistic for the planner):

create index concurrently on task_task_execution_sta=
te_idx async.task using btree ((async.task_execution_state(task)));

analyze async.task;

This can't help performance, as the index...
CREATE INDEX ON async.task(concurrency_pool, priority, entered)
WHERE async.task_execution_state(task) =3D 'READY';=

...is very precisely configured to provide exactly what's needed; I need tasks for that exact pool in that exact order if and only if ready.=C2=A0 The partial predica= te is designed to keep the index nice and small since only a small portion of tasks would be eligible at any specific time.

The index I suggested was not intended to be used by such queries, it's only a way to provide statistics for the planner as `create statistics` on expression is not working here.

It might not be enough and require additional columns (in that case it will be replacement for your index) or perhaps elevated statistics target. It could even make things worse but I'm sure you have ways to test that before putting it on an important database.

It should help if planner underestimate number of rows but even then total estimation won't be perfect when we have 2 conditions on state that obviously statistically dependent. Combining both conditions on application side would work much better if that is possible...

What would also might help there - output of `explain (analyze, buffers)` of a query that really had a bad plan and executes in seconds with sizes of indexes. And=C2=A0same output but with `set enable_sort =3D off` to see plan that supposed to be better. And just in case number of live/dead tuples in that table from pg_stat_user_tables.


Roger.= =C2=A0 Turns out, I don't have access to pg_statistic (working on that)= .=C2=A0 Here are the plans with buffers:

orchestrator_service_user@orchestr= ator=3D> explain (analyze, buffers) select * from async.task where async= .task_execution_state(task.*) =3D 'READY'::async.task_execution_sta= te_t =C2=A0and concurrency_pool =3D '065.laqjjj_live' order by prio= rity, entered limit 10;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------
=C2=A0Limit =C2=A0(cost=3D25.88= ..25.90 rows=3D10 width=3D563) (actual time=3D35.024..35.026 rows=3D0 loops= =3D1)
=C2=A0 =C2=A0Buffers: shared hit=3D12542
=C2=A0 =C2=A0-> =C2= =A0Sort =C2=A0(cost=3D25.88..26.33 rows=3D179 width=3D563) (actual time=3D3= 5.023..35.024 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort= Key: priority, entered
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Method: q= uicksort =C2=A0Memory: 25kB
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: s= hared hit=3D12542
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Bitmap H= eap Scan on task =C2=A0(cost=3D9.23..22.01 rows=3D179 width=3D563) (actual = time=3D34.989..34.990 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Recheck Cond: ((async.task_execution_state(task.= *) =3D ANY ('{READY,RUNNING,YIELDED}'::async.task_execution_state_t= [])) AND (concurrency_pool =3D '065.laqjjj_live'::text) AND (async.= task_execution_state(task.*
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0Buffers: shared hit=3D12536
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0BitmapAnd =C2=A0(cost=3D9.23..9.23 rows= =3D3 width=3D0) (actual time=3D34.979..34.980 rows=3D0 loops=3D1)
=C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffe= rs: shared hit=3D12536
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Bitmap Index Scan on task_task_id_id= x =C2=A0(cost=3D0.00..4.38 rows=3D575191 width=3D0) (actual time=3D34.882..= 34.883 rows=3D97 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit= =3D12502
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0-> =C2=A0Bitmap Index Scan on task_concurrency_pool_priorit= y_entered_idx =C2=A0(cost=3D0.00..4.51 rows=3D179 width=3D0) (actual time= =3D0.092..0.093 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (= concurrency_pool =3D '065.laqjjj_live'::text)
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Buffers: shared hit=3D34
=C2=A0Planning:
=C2=A0 =C2=A0Buffers: = shared hit=3D350
=C2=A0Planning Time: 1.571 ms
=C2=A0Execution Time: = 35.091 ms
(20 rows)

orchestrator_service_user@orchestrator=3D> set enable_= sort to false;
SET

orchestrator_service_user@orchestrator=3D> = explain (analyze, buffers) select * from async.task where async.task_execut= ion_state(task.*) =3D 'READY'::async.task_execution_state_t =C2=A0a= nd concurrency_pool =3D '065.laqjjj_live' order by priority, entere= d limit 10;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
---------= ---------------------------------------------------------------------------= -------------------------------------------------------------------------=C2=A0Limit =C2=A0(cost=3D0.50..39.87 rows=3D10 width=3D563) (actual time= =3D0.091..0.092 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Buffers: shared hit=3D3= 4
=C2=A0 =C2=A0-> =C2=A0Index Scan using task_concurrency_pool_priori= ty_entered_idx on task =C2=A0(cost=3D0.50..705.21 rows=3D179 width=3D563) (= actual time=3D0.090..0.091 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Index Cond: (concurrency_pool =3D '065.laqjjj_live'::text= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D34
=C2=A0Pl= anning Time: 0.251 ms
=C2=A0Execution Time: 0.110 ms
(7 rows)

What I'm driving at here is that while s= tatistics contribute to the issue, it's somewhat baffling that postgres= estimates the 'good' plan slower than the 'bad' plan.=C2= =A0 Note that it incorrectly estimated 500k rows in the heap scan=C2=A0 to = avoid 179 heap fetches, since scanning the index itself is a wash, with a n= on-trivial recheck.=C2=A0 So I see this as a planning problem because, even= with the estimated stats the plan makes no sense.

What seems to have suppressed the bad plan is:=C2=A0
REINDEX IND= EX async.task_task_id_idx;

yielding this plan:
orchestrator_service_user@orchestrator=3D> explain (analyze, buff= ers) select * from async.task where async.task_execution_state(task.*) =3D = 'READY'::async.task_execution_state_t =C2=A0and concurrency_pool = =3D '065.laqjjj_live' order by priority, entered limit 10;
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
---------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------
=C2=A0Limit =C2= =A0(cost=3D0.50..39.88 rows=3D10 width=3D563) (actual time=3D0.111..0.112 r= ows=3D0 loops=3D1)
=C2=A0 =C2=A0Buffers: shared hit=3D34
=C2=A0 =C2= =A0-> =C2=A0Index Scan using task_concurrency_pool_priority_entered_idx = on task =C2=A0(cost=3D0.50..685.64 rows=3D174 width=3D563) (actual time=3D0= .110..0.110 rows=3D0 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index = Cond: (concurrency_pool =3D '065.laqjjj_live'::text)
=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D34
=C2=A0Planning:
=C2= =A0 =C2=A0Buffers: shared hit=3D311 read=3D41
=C2=A0 =C2=A0I/O Timings: = shared read=3D23.052
=C2=A0Planning Time: 24.972 ms
=C2=A0Execution T= ime: 0.152 ms

...with no planner tweaks.

forcing the bad plan,

orchestrator= _service_user@orchestrator=3D> set enable_indexscan to false;
SET
= Time: 98.377 ms
orchestrator_service_user@orchestrator=3D> explain (a= nalyze, buffers) select * from async.task where async.task_execution_state(= task.*) =3D 'READY'::async.task_execution_state_t =C2=A0and concurr= ency_pool =3D '065.laqjjj_live' order by priority, entered limit 10= ;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN
-------------= ---------------------------------------------------------------------------= -------------------------------------------------------------------------=C2=A0Limit =C2=A0(cost=3D745.70..745.73 rows=3D10 width=3D563) (actual t= ime=3D0.103..0.103 rows=3D0 loops=3D1)
=C2=A0 =C2=A0Buffers: shared hit= =3D40
=C2=A0 =C2=A0-> =C2=A0Sort =C2=A0(cost=3D745.70..746.14 rows=3D= 174 width=3D563) (actual time=3D0.102..0.102 rows=3D0 loops=3D1)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0Sort Key: priority, entered
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Sort Method: quicksort =C2=A0Memory: 25kB
=C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D40
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0-> =C2=A0Bitmap Heap Scan on task =C2=A0(cost=3D4.55..741.94 r= ows=3D174 width=3D563) (actual time=3D0.068..0.069 rows=3D0 loops=3D1)
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Recheck Cond: ((conc= urrency_pool =3D '065.laqjjj_live'::text) AND (async.task_execution= _state(task.*) =3D 'READY'::async.task_execution_state_t))
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D3= 4
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Bit= map Index Scan on task_concurrency_pool_priority_entered_idx =C2=A0(cost=3D= 0.00..4.51 rows=3D174 width=3D0) (actual time=3D0.064..0.064 rows=3D0 loops= =3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Index Cond: (concurrency_pool =3D '065.laqjjj_live'::text= )
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Buffers: shared hit=3D34
=C2=A0Planning:
=C2=A0 =C2=A0Buffers: = shared hit=3D1
=C2=A0Planning Time: 0.258 ms
=C2=A0Execution Time: 0.= 136 ms

Point being, bloat is definitely a factor h= ere. However, the database seems to be doing the opposite of what's exp= ected, in the presence of bloat, it's veering toward a more bloat sensi= tive plan.

merlin
=C2=A0
--00000000000057ad44064d542b5f--