On 10/8/17 3:37 PM, Tom Lane wrote:
Jim Nasby <jim@xxxxxxxxx> writes:
On 10/8/17 2:34 PM, Tom Lane wrote:
Why has this indexscan's cost estimate changed so much?
Great question... the only thing that sticks out is the coalesce(). Let
me see if an analyze with a higher stats target changes anything. FWIW,
the 9.6 database is copied from the 9.4 one once a week and then
pg_upgraded. I'm pretty sure an ANALYZE is part of that process.
Hm, now that I see the SubPlan in there, I wonder whether 9.6 is
accounting more conservatively for the cost of the subplan. It
probably is assuming that the subplan gets run for each row fetched
from the index, although the loops and rows-removed counts show
that the previous filter conditions reject 99% of the fetched rows.
But that code looks the same in 9.4, so I don't understand why
the 9.4 estimate isn't equally large ...
Besides the analyze issue, the other part of this is
asdidata@xxxxxxxxxxxx/20106> select
pg_size_pretty(pg_relation_size('bdata_forks'));
pg_size_pretty
----------------
106 GB
(1 row)
asdidata@xxxxxxxxxxxx/20106> select relpages::bigint*8192/reltuples from
pg_class where relname='bdata_forks';
?column?
------------------
185.559397863791
(1 row)
With an effective_cache_size of 200GB that's not really helping things.
But it's also another example of the planner's reluctance towards index
scans.
--
Jim C. Nasby, Data Architect jim@xxxxxxxxx
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
