O filtro é aplicado após DISTINCT ON

Há um script no final que criará totalmente o esquema e o preencherá com dados de amostra.

Esquema

Considere estas duas tabelas:

Tabela de polígonos:

CREATE TABLE my_polygon (
    my_polygon_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    value1 NUMERIC NOT NULL,
    value2 NUMERIC NOT NULL,
    value3 NUMERIC NOT NULL,
    geom GEOMETRY(Polygon) NOT NULL
)
;

CREATE INDEX ON my_polygon (common_id);
CREATE INDEX ON my_polygon USING GIST (common_id, geom);

Tabela de pontos contidos dentro de polígonos:

CREATE TABLE my_point (
    my_point_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    pointvalue NUMERIC NOT NULL,
    geom GEOMETRY(Point) NOT NULL
);

CREATE INDEX ON my_point (common_id);
CREATE INDEX ON my_point USING GIST (common_id, geom);

O fato de estar usando geometrias não está estritamente relacionado ao problema aqui; no entanto, acho que isso torna os motivos do que estou tentando fazer muito mais claros.

consulta de problema

O problema é que existem sobreposições muito pequenas e insignificantes entre os polígonos. (Tentar limpá-los realmente não é uma opção. As sobreposições vêm de algum tipo de erro de ponto flutuante ao gerá-los, tanto quanto posso imaginar.) Mas alguns pontos podem cair dentro dessas pequenas sobreposições, resultando em duas linhas quando eu JOINos com base na contenção. Mas, na verdade, cada ponto só deve ser associado a um único polígono. Quando um cai dentro de dois deles, realmente não importa com qual deles ele acaba associado, então é bom fazer a consulta, apenas escolha um, assim:

SELECT DISTINCT ON (my_point.my_point_id)
    my_polygon.*,
    my_point.my_point_id,
    my_point.pointvalue,
    my_point.geom AS pointgeom
FROM my_polygon
JOIN my_point ON my_point.common_id = my_polygon.common_id AND ST_Contains(my_polygon.geom, my_point.geom)
WHERE my_polygon.common_id = 1
ORDER BY my_point.my_point_id, my_polygon.my_polygon_id

Como na consulta acima, normalmente desejo SELECTbasear-me no arquivo common_id. Esta consulta funciona bem. Seu plano de consulta se parece com isto:

No entanto, essa é a lógica de que preciso em várias consultas diferentes, por isso queria colocá-la em uma exibição. O resultado é que, no que diz respeito ao planejador de consulta, a consulta se parece com isso:

SELECT *
FROM (
    SELECT DISTINCT ON (my_point.my_point_id)
        my_polygon.*,
        my_point.my_point_id,
        my_point.pointvalue,
        my_point.geom AS pointgeom
    FROM my_polygon
    JOIN my_point ON my_point.common_id = my_polygon.common_id AND ST_Contains(my_polygon.geom, my_point.geom)
    ORDER BY my_point.my_point_id, my_polygon.my_polygon_id
) point_with_polygon
WHERE common_id = 1

O resultado é que agora o PostgreSQL filtra common_id depois de executar o DISTINCT ON, o que significa que ele tem JOINa totalidade de ambas as tabelas. Aqui está o plano de consulta:

Como posso permitir que o PostgreSQL envie o filtro para uma parte inicial da consulta e ainda coloque a consulta geral em uma exibição?

Estou preso no PG 9.3 agora, mas atualizar para 9.5 pode ser uma opção.

Esquema e script de dados de amostra

Requer PostGIS. (É por isso que não há SQL Fiddle.)

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- DROP FUNCTION ST_GeneratePoints(geometry, numeric);
DO $doblock$
BEGIN
    IF NOT EXISTS(SELECT * FROM pg_proc WHERE UPPER(proname) = UPPER('ST_GeneratePoints')) THEN
        -- Create naive ST_GeneratePoints if version of PostGIS is not new enough
        CREATE FUNCTION ST_GeneratePoints(g geometry, npoints numeric)
            RETURNS geometry
            VOLATILE
            RETURNS NULL ON NULL INPUT
            LANGUAGE plpgsql
            AS $$
            DECLARE
                num_to_generate INTEGER := npoints::INTEGER;
                adjustment CONSTANT FLOAT := 0.00000000001;
                x_min FLOAT := ST_XMin(g) + adjustment;
                x_max FLOAT := ST_XMax(g) - adjustment;
                y_min FLOAT := ST_YMin(g) + adjustment;
                y_max FLOAT := ST_YMax(g) - adjustment;
                temp_result GEOMETRY[];
                result_array GEOMETRY[] := ARRAY[]::GEOMETRY[];
            BEGIN
                IF ST_IsEmpty(g) THEN
                    RAISE EXCEPTION 'Cannot generate points inside an empty geometry';
                END IF;

                IF ST_Dimension(g) < 2 THEN
                    RAISE EXCEPTION 'Only polygons supported';
                END IF;

                -- Reduce number of loops to reduce slow array_cat calls
                WHILE num_to_generate > 0 LOOP
                    SELECT ARRAY_AGG(contained.point) INTO temp_result
                    FROM (
                        SELECT point
                        FROM (
                            SELECT ST_MakePoint(
                                x_min + random() * (x_max - x_min),
                                y_min + random() * (y_max - y_min)
                            ) point
                            -- Generate extras to reduce number of loops
                            --
                            -- Each point has a probability of  ST_Area(g) / ST_Area(ST_Envelope(g))  to fall within the polygon.
                            -- So on average, we expect ST_Area(g) / ST_Area(ST_Envelope(g)) of the points generated to fall within.
                            -- Generating  ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate  points means that on average, we'll
                            -- get
                            --
                            --     ST_Area(g) / ST_Area(ST_Envelope(g)) * ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate
                            --      = num_to_generate
                            --
                            -- points within the polygon. (Notice the numerators and denominators cancel out.) This means we'll 
                            -- only run one loop about half the time without generating an excessive number of points.
                            --
                            -- Generate at least 20 to avoid a lot of loops for small numbers, though.
                            FROM generate_series(1, GREATEST(20, CEIL(ST_Area(ST_Envelope(g)) / ST_Area(g) * num_to_generate)::INTEGER))
                        ) candidate
                        WHERE ST_Contains(g, candidate.point)
                        -- Filter out extras if we have too many matches
                        LIMIT num_to_generate
                    ) contained
                    ;
                    IF ARRAY_LENGTH(temp_result, 1) > 0 THEN
                        result_array := array_cat(result_array, temp_result);
                        num_to_generate := npoints - COALESCE(ARRAY_LENGTH(result_array, 1), 0);
                    END IF;
                END LOOP;
                RETURN (SELECT ST_Union(point) FROM UNNEST(result_array) result (point));
            END;
            $$;
        RAISE NOTICE 'Created ST_GeneratePoints';
    ELSE
        RAISE NOTICE 'ST_GeneratePoints exists';
    END IF;
END
$doblock$
;

DROP TABLE IF EXISTS my_polygon;

CREATE TABLE my_polygon (
    my_polygon_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    value1 NUMERIC NOT NULL,
    value2 NUMERIC NOT NULL,
    value3 NUMERIC NOT NULL,
    geom GEOMETRY(Polygon) NOT NULL
)
;

CREATE INDEX ON my_polygon (common_id);
CREATE INDEX ON my_polygon USING GIST (common_id, geom);


WITH common AS (
    SELECT
        common_id,
        random() * 5000 AS common_x_translate,
        random() * 5000 AS common_y_translate
    FROM (
        SELECT TRUNC(random() * 1000) + 1 AS common_id
        FROM generate_series(1, 100)
        UNION 
        SELECT 1
    ) a
),
geom_set_with_small_overlaps AS (
    SELECT
        ST_MakeEnvelope(
            x.translate, 
            y.translate, 
            x.translate + 1.1, 
            y.translate + 1.1
        ) AS geom
    FROM
        generate_series(0, 9) x (translate),
        generate_series(0, 9) y (translate)
)
INSERT INTO my_polygon (common_id, value1, value2, value3, geom)
SELECT
    common_id,
    random() * 100,
    random() * 100,
    random() * 100,
    ST_Translate(geom, common_x_translate, common_y_translate)
FROM common, geom_set_with_small_overlaps
;

DROP TABLE IF EXISTS my_point;

CREATE TABLE my_point (
    my_point_id SERIAL PRIMARY KEY,
    common_id INTEGER NOT NULL,
    pointvalue NUMERIC NOT NULL,
    geom GEOMETRY(Point) NOT NULL
);

INSERT INTO my_point (common_id, pointvalue, geom)
SELECT
    common_id,
    random() * 100,
    (ST_Dump(ST_GeneratePoints(extent, FLOOR(5000 + random() * 15000)::NUMERIC))).geom
FROM (
    SELECT
        common_id,
        -- Small negative buffer prevents lying on the outer edge
        ST_Buffer(ST_Extent(geom), - 0.0001) AS extent
    FROM my_polygon
    GROUP BY common_id
) common
UNION ALL
SELECT
    common_id,
    random() * 100,
    (ST_Dump(ST_GeneratePoints(intersection, TRUNC(random() * 5)::NUMERIC))).geom
FROM (
    SELECT
        p1.common_id,
        p1.my_polygon_id AS id1,
        p2.my_polygon_id AS id2,
        ST_Intersection(p1.geom, p2.geom) AS intersection
    FROM my_polygon p1
    JOIN my_polygon p2 ON (
        p1.my_polygon_id < p2.my_polygon_id AND
        p1.common_id = p2.common_id AND
        ST_Intersects(p1.geom, p2.geom)
    )
) a
;

CREATE INDEX ON my_point (common_id);
CREATE INDEX ON my_point USING GIST (common_id, geom);

Você provavelmente quer VACUUM ANALYZEdepois disso.

Planos de consulta como texto

WHEREcláusula dentro (bom desempenho):

Unique  (cost=1195.74..1207.74 rows=2400 width=216)
  ->  Sort  (cost=1195.74..1201.74 rows=2400 width=216)
        Sort Key: my_point.my_point_id, my_polygon.my_polygon_id
        ->  Nested Loop  (cost=5.34..1060.99 rows=2400 width=216)
              ->  Bitmap Heap Scan on my_polygon  (cost=4.93..191.74 rows=100 width=164)
                    Recheck Cond: (common_id = 1)
                    ->  Bitmap Index Scan on my_polygon_common_id_geom_idx  (cost=0.00..4.90 rows=100 width=0)
                          Index Cond: (common_id = 1)
              ->  Index Scan using my_point_common_id_geom_idx on my_point  (cost=0.41..8.68 rows=1 width=52)
                    Index Cond: ((common_id = 1) AND (my_polygon.geom && geom))
                    Filter: _st_contains(my_polygon.geom, geom)

WHEREcláusula externa (desempenho ruim):

Subquery Scan on a  (cost=209447.85..215842.18 rows=1827 width=212)
  Filter: (a.common_id = 1)
  ->  Unique  (cost=209447.85..211274.80 rows=365390 width=212)
        ->  Sort  (cost=209447.85..210361.33 rows=365390 width=212)
              Sort Key: my_point.my_point_id, my_polygon.my_polygon_id
              ->  Nested Loop  (cost=0.41..63285.00 rows=365390 width=212)
                    ->  Seq Scan on my_polygon  (cost=0.00..338.00 rows=9800 width=164)
                    ->  Index Scan using my_point_common_id_geom_idx on my_point  (cost=0.41..6.41 rows=1 width=52)
                          Index Cond: ((common_id = my_polygon.common_id) AND (my_polygon.geom && geom))
                          Filter: _st_contains(my_polygon.geom, geom)