Re: Consultas sobre vistas

Conrado Blasetti escribió:
> Mi Gran Amigo entra por seq scan, la consulta va orientada mas a la teoría
> de las vistas. Tengo pocos registros y una teoría sería que al haber pocos
> registros en las tablas (menos de 100 en todas las tablas), y otra que entre
> full a todas para luego filtrar mediante el where de la vista (aunque no
> creo esto ultimo)

Con respecto a la teoria: se puede hacer push-down de condiciones desde una
consulta externa hacia "adentro de la definicion de la vista". Hay
muchas limitaciones con respecto a las situaciones donde este pushdown
puede hacerse.

Es posible que funcione en tu caso, pero no se. Haz una tabla grande y
prueba.

El codigo relevante parece ser este (src/backend/optimizer/path/allpaths.c)

/*
* qual_is_pushdown_safe - is a particular qual safe to push down?
*
* qual is a restriction clause applying to the given subquery (whose RTE
* has index rti in the parent query).
*
* Conditions checked here:
*
* 1. The qual must not contain any subselects (mainly because I'm not sure
* it will work correctly: sublinks will already have been transformed into
* subplans in the qual, but not in the subquery).
*
* 2. The qual must not refer to any subquery output columns that were
* found to have inconsistent types across a set operation tree by
* subquery_is_pushdown_safe().
*
* 3. If the subquery uses DISTINCT ON, we must not push down any quals that
* refer to non-DISTINCT output columns, because that could change the set
* of rows returned. This condition is vacuous for DISTINCT, because then
* there are no non-DISTINCT output columns, but unfortunately it's fairly
* expensive to tell the difference between DISTINCT and DISTINCT ON in the
* parsetree representation. It's cheaper to just make sure all the Vars
* in the qual refer to DISTINCT columns.
*
* 4. We must not push down any quals that refer to subselect outputs that
* return sets, else we'd introduce functions-returning-sets into the
* subquery's WHERE/HAVING quals.
*/
static bool
qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual,
bool *differentTypes)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.