Re: [PERFORM] query of partitioned object doesnt use index in qa

Very helpful thank you for the additional insight - I'd never checked into pg_stats and that does reveal a difference in the distribution of the validation_status_code between qa and production: prod: │ most_common_vals │ {P,F} │ │ most_common_freqs │

Re: [PERFORM] query of partitioned object doesnt use index in qa

On 21 September 2017 at 04:15, Mike Broers 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

Re: [PERFORM] query of partitioned object doesnt use index in qa

I was able to add the suggested indexes like stage.event__00075000((body->>'SID'::text)); and indeed these helped the QA environment use those indexes instead of sequence scanning. I'm still perplexed by my original question, why production uses the partition index and qa does not? Index Scan

Re: [PERFORM] query of partitioned object doesnt use index in qa

On 09/16/2017 12:05 AM, Tom Lane wrote: > Andres Freund writes: >> I'm wondering if we should extend the new CREATE STATISTICS >> framework to be able to do that without requiring an index. > > I think that's already on the roadmap --- it's one of the reasons we > ended up

Re: [PERFORM] query of partitioned object doesnt use index in qa

Andres Freund writes: > I'm wondering if we should extend the new CREATE STATISTICS framework to > be able to do that without requiring an index. I think that's already on the roadmap --- it's one of the reasons we ended up with a SELECT-like syntax for CREATE STATISTICS.

Re: [PERFORM] query of partitioned object doesnt use index in qa

On September 15, 2017 1:42:23 PM PDT, Tom Lane wrote: >One thing you could consider doing about this is creating an index >on (body ->> 'SID'::text), which would prompt ANALYZE to gather >statistics >about that expression. Even if the index weren't actually used in the

Re: [PERFORM] query of partitioned object doesnt use index in qa

That makes a lot of sense, thanks for taking a look. An index like you suggest would probably further improve the query. Is that suggestion sidestepping the original problem that production is evaluating the landing_id bit with the partition index and qa is sequence scanning instead? AND

Re: [PERFORM] query of partitioned object doesnt use index in qa

Mike Broers writes: > If Im reading this correctly postgres thinks the partition will return 6.5 > million matching rows but actually comes back with 162k. Is this a case > where something is wrong with the analyze job? You've got a lot of scans there that're using conditions

Re: [PERFORM] query of partitioned object doesnt use index in qa

Query finally came back with an explain analyze :) If Im reading this correctly postgres thinks the partition will return 6.5 million matching rows but actually comes back with 162k. Is this a case where something is wrong with the analyze job? Seq Scan on event__00071000 e_4

Re: [PERFORM] query of partitioned object doesnt use index in qa

Thanks for the suggestions, I'll futz with random_page_cost and effective_cache_size a bit and follow up, as well as try to provide an explain analyze on both (if the longer query ever returns!) Most appreciated. On Wed, Sep 13, 2017 at 4:57 PM, David Rowley

Re: [PERFORM] query of partitioned object doesnt use index in qa

On 14 September 2017 at 08:28, Mike Broers wrote: > I have a query of a partitioned table that uses the partition index in > production but uses sequence scans in qa. The only major difference I can > tell is the partitions are much smaller in qa. In production the partitions