Re: Can I force a query plan to materialise part?

Hi,

On 2 October 2012 13:28, Toby Corkindale
<toby(dot)corkindale(at)strategicdata(dot)com(dot)au> wrote:
>>> I have a query that joins two views, and takes 28 seconds to run.
>>> However if I create temporary tables that contain the contents of each
>>> view,
>>> and then join them, the total time is 1.3 seconds.
>>
>>
>> try "offset 0" (or you can tweak statistics collector to get better
>> estimates):
>> select ... from (select * from view offset 0) as v ....
>
> I wish I could work out what's wrong with the statistics that cause the
> query plan to go awry.. the tables aren't actually very large and I've
> played with the statistics setup previously and it seemed right..

Try this (in single session):
explain analyze <your query>
set default_statistics_target = 1000 (or 500 or 250; 1000 might take ages)
analyze table_1; analyze table_2; ..., analyze table_N; (all involved
tables in your query)
explain analyze <your query>

and compare explains outputs. If estimates are very different
(magnitude or two) then you should tweak autovacuum frequency and set
per column statistics (ie. keep default_statistics_target = 100
(default), and change it on per column basis) but this could be
tedious:

Although per-column tweaking of ANALYZE frequency might not be very
productive, you might find it worthwhile to do per-column adjustment
of the level of detail of the statistics collected by ANALYZE. Columns
that are heavily used in WHERE clauses and have highly irregular data
distributions might require a finer-grain data histogram than other
columns. See ALTER TABLE SET STATISTICS, or change the database-wide
default using the default_statistics_target configuration parameter.
Also, by default there is limited information available about the
selectivity of functions. However, if you create an expression index
that uses a function call, useful statistics will be gathered about
the function, which can greatly improve query plans that use the
expression index.
http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-STATISTICS

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)