2011/7/27 Alvaro Herrera <[email protected]>: >> >> Para opciones de repliacion planeadas mas adelante voy a pasarme a la >> version 9 de postgres ( ahorita estoy en la 8.4), crees que ayude >> tambien a las consultas? > > Puede ser. Todas las versiones traen una u otra mejora al optimizador, > así que es bien posible que alguna de tus consultas mejore de alguna > forma. O quizás no. No me acuerdo qué cambios hubo en el optimizador > en 9.0. >
Estas parecen ser las que mas afectarían al rendimiento (de la primera me habia olvidado por completo) """ E.5.3.1.2. Performance Allow per-tablespace values to be set for sequential and random page cost estimates (seq_page_cost/random_page_cost) via ALTER TABLESPACE ... SET/RESET (Robert Haas) Improve performance and reliability of EvalPlanQual rechecks in join queries (Tom Lane) UPDATE, DELETE, and SELECT FOR UPDATE/SHARE queries that involve joins will now behave much better when encountering freshly-updated rows. Improve performance of TRUNCATE when the table was created or truncated earlier in the same transaction (Tom Lane) Improve performance of finding inheritance child tables (Tom Lane) E.5.3.1.3. Optimizer Remove unnecessary outer joins (Robert Haas) Outer joins where the inner side is unique and not referenced above the join are unnecessary and are therefore now removed. This will accelerate many automatically generated queries, such as those created by object-relational mappers (ORMs). Allow IS NOT NULL restrictions to use indexes (Tom Lane) This is particularly useful for finding MAX()/MIN() values in indexes that contain many null values. Improve the optimizer's choices about when to use materialize nodes, and when to use sorting versus hashing for DISTINCT (Tom Lane) Improve the optimizer's equivalence detection for expressions involving boolean <> operators (Tom Lane) E.5.3.1.4. GEQO Use the same random seed every time GEQO plans a query (Andres Freund) While the Genetic Query Optimizer (GEQO) still selects random plans, it now always selects the same random plans for identical queries, thus giving more consistent performance. You can modify geqo_seed to experiment with alternative plans. Improve GEQO plan selection (Tom Lane) This avoids the rare error "failed to make a valid plan", and should also improve planning speed. E.5.3.1.5. Optimizer Statistics Improve ANALYZE to support inheritance-tree statistics (Tom Lane) This is particularly useful for partitioned tables. However, autovacuum does not yet automatically re-analyze parent tables when child tables change. Improve autovacuum's detection of when re-analyze is necessary (Tom Lane) Improve optimizer's estimation for greater/less-than comparisons (Tom Lane) When looking up statistics for greater/less-than comparisons, if the comparison value is in the first or last histogram bucket, use an index (if available) to fetch the current actual column minimum or maximum. This greatly improves the accuracy of estimates for comparison values near the ends of the data range, particularly if the range is constantly changing due to addition of new data. Allow setting of number-of-distinct-values statistics using ALTER TABLE (Robert Haas) This allows users to override the estimated number or percentage of distinct values for a column. This statistic is normally computed by ANALYZE, but the estimate can be poor, especially on tables with very large numbers of rows. """ -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación - Enviado a la lista de correo pgsql-es-ayuda ([email protected]) Para cambiar tu suscripción: http://www.postgresql.org/mailpref/pgsql-es-ayuda
