Re: Ayuda Consulta de fechas

2011/7/27 Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>:
>>
>> 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