From: Gunther <raj@gusw.net>
Subject: Re: Re: OLAP/reporting queries fall into nested loops over
 seq scans or other horrible planner choices
To: pgsql-performance@postgresql.org
References: <f1b85c37-e4b5-c158-5f67-a780a2191e20@gusw.net>
 <1509611428.3268.5.camel@cybertec.at> <1509701327868-0.post@n3.nabble.com>
 <CAMjNa7dy23vwG4UEoTe+xcgJF6xj7bptVra_-BgieGTpJUYOSg@mail.gmail.com>
 <9a76f13e-cdb1-1d8d-2178-67e6dcf169bc@gusw.net>
Message-ID: <9ed2bdd0-7094-7e7f-d276-3647a10f635f@gusw.net>
Date: Fri, 3 Nov 2017 10:51:31 -0400
User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: <9a76f13e-cdb1-1d8d-2178-67e6dcf169bc@gusw.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Just throwing out some more innovative ideas.

Materialized join tables, I have read somewhere. OK, difficult to keep 
consistent with transactions. Forget that.

But, why not collect statistics on every join that is processed, even if 
the query is interrupted. Then as more and more plans are run, and 
interrupted for being too slow, statistics on the joins are collected 
and can inform the optimizer next time not to use that approach.

Would work like magic for a user.

User writes a query. It runs 3 minutes and as no result. User interrupts 
the query (THANKS PgSQL for allowing that, unlike Oracle!). Now the 
statistics has already been gathered.

User reruns the query, not changing anything. Because the statistics on 
(some of) the joins has been gathered, at least with an initial sample, 
now the planner will likely choose a different plan. Say, now the 
results come in at 2 minutes and the user is satisfied. But still more 
complete statistics was collected.

Now the user changes a few query parameters and runs the query again, or 
puts it into a more complex query. This time the planner has even more 
statistics and chooses an even better plan. And lo and behold now the 
results come in at 10 seconds!

At no point did the user have to analyze the explain plan, come up with 
hints and tricks and nudges to the optimizer. And at no point did the 
user have to become DBA to run some outlandish PL/SQL procedures for 
which he does not have the license key or the special privileges.

But until that is done, please put in the pg_hint_plan.c. Hints don't 
hurt. If you don't like them, don't use them.,

regards,
-Gunther


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance