Hi, On 2 October 2012 13:28, Toby Corkindale <toby.corkindale@xxxxxxxxxxxxxxxxxxxx> 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.ivanic@xxxxxxxxx) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
