Maybe this is already solved in more advanced releases, but just in
case.
VIEW active_users:
SELECT * FROM users WHERE active AND ((field IS NULL) OR (NOT field));
Table users has index on text field login.
EXPLAIN SELECT * from active_users where login='xxx';
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using active_users on users u (cost=0.00..5.97 rows=1
width=131)
Index Cond: ("login" = 'xxx'::text)
Filter: (active AND ((field1 IS NULL) OR (NOT field1)))
So far, everything OK.
Now, combined (sorry for the convoluted query, it is build
automatically by an app).
EXPLAIN SELECT DISTINCT p.id
FROM partes_tecnicos p,
rel_usr_sector_parte_tecnico r, active_users u
WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id AND
u.login='xxx' AND r.id_sector=p.id_sector_actual AND
p.id_cola_por_ambito=1)
OR p.id_cola_por_ambito=1)
AND p.id_situacion!=6;
-----
Unique (cost=1016.84..22057814.97 rows=219 width=4)
-> Nested Loop (cost=1016.84..19607287.64 rows=980210931 width=4)
-> Nested Loop (cost=8.07..2060.25 rows=100959 width=4)
-> Index Scan using partes_tecnicos_pkey on
partes_tecnicos p (cost=0.00..33.00 rows=219 width=4)
Filter: ((id_cola_por_ambito = 1) AND
(id_situacion <> 6))
-> Materialize (cost=8.07..12.68 rows=461 width=0)
-> Seq Scan on rel_usr_sector_parte_tecnico r
(cost=0.00..7.61 rows=461 width=0)
-> Materialize (cost=1008.77..1105.86 rows=9709 width=0)
-> Seq Scan on users u (cost=0.00..999.06
rows=9709 width=0)
Filter: (active AND ((field1 IS NULL) OR
(NOT field1)))
Notice the seq. scan on users.
It is solved using:
EXPLAIN SELECT DISTINCT p.id
FROM partes_tecnicos p, pt.rel_usr_sector_parte_tecnico r,
(SELECT id FROM active_users WHERE
login='xxx') u
WHERE ((r.id_parte_tecnico=p.id AND r.id_usr=u.id
AND r.id_sector=p.id_sector_actual AND p.id_cola_por_ambito=1)
OR p.id_cola_por_ambito=1
) AND p.id_situacion!=6;
-------------------------------------------------------------------------------------------------------------
Unique (cost=18.65..2323.23 rows=219 width=4)
-> Nested Loop (cost=18.65..2070.83 rows=100959 width=4)
-> Index Scan using partes_tecnicos_pkey on partes_tecnicos
p (cost=0.00..33.00 rows=219 width=4)
Filter: ((id_cola_por_ambito = 1) AND (id_situacion <>
6))
-> Materialize (cost=18.65..23.26 rows=461 width=0)
-> Nested Loop (cost=0.00..18.19 rows=461 width=0)
-> Index Scan using active_users on users u
(cost=0.00..5.97 rows=1 width=0)
Index Cond: ("login" = 'xxx'::text)
Filter: (active AND ((field1 IS NULL)
OR (NOT field1)))
-> Seq Scan on rel_usr_sector_parte_tecnico r
(cost=0.00..7.61 rows=461 width=0)
(10 rows)
Thanks!
Fernando.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
