On 21 September 2017 at 04:15, Mike Broers <mbro...@gmail.com> wrote: > Ultimately I think this is just highlighting the need in my environment to > set random_page_cost lower (we are on an SSD SAN anyway..), but I dont think > I have a satisfactory reason by the row estimates are so bad in the QA > planner and why it doesnt use that partition index there.
Without the index there are no stats to allow the planner to perform a good estimate on "e.body->>'SID' is not null", so it applies a default of 99.5%. So, as a simple example, if you have a partition with 1 million rows. If you apply 99.5% to that you get 995000 rows. Now if you add the selectivity for "e.validation_status_code = 'P' ", let's say that's 50%, the row estimate for the entire WHERE clause would be 497500 (1000000 * 0.995 * 0.5). Since the 99.5% is applied in both cases, then the only variable part is validation_status_code. Perhaps validation_status_code = 'P' is much more common in QA than in production. You can look at the stats as gathered by ANALYZE with: \x on select * from pg_stats where tablename = 'event__99999999' and attname = 'validation_status_code'; \x off -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
