Hi all,
I'm running PostgreSQL v 8.1.4 and found a query that returns tuples
that does not satisfy the WHERE clause, the query is:
select * into errores_20071 from (
select r.id, r.trayectoria_id, r.grupo_id, regacd.insc_registra_grupo(trayectoria_id, grupo_id, true, false, true)
aserror
from regacd.registro r join regacd.grupo g on (g.id = r.grupo_id)
where g.año_semestre = 20071 and g.tipo_id = 'a') x
where error is not null;
A self-contained database schema is here:
Unfortunately I cannot post the data set but I'm willing to give
access to my machine to test the problem.
While trying to create a self contained test case I found that the
query returns the correct answer before analyzing:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=41.61..31193.44 rows=36 width=12)
-> Index Scan using "AsignaturClaveGrupoÚnicaPorAñoSemestre2" on grupo g (cost=0.00..14.03 rows=3 width=4)
Index Cond: (("año_semestre" = 20071) AND (tipo_id = 'a'::"char"))
-> Bitmap Heap Scan on registro r (cost=41.61..10305.22 rows=7031 width=12)
Recheck Cond: ("outer".id = r.grupo_id)
Filter: (insc_registra_grupo(trayectoria_id, grupo_id, true, false, true) IS NOT NULL)
-> Bitmap Index Scan on registro_grupo (cost=0.00..41.61 rows=7031 width=0)
Index Cond: ("outer".id = r.grupo_id)
(8 filas)
but does not after I run analyze:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Hash Join (cost=1166.75..44109.74 rows=34184 width=12)
Hash Cond: ("outer".grupo_id = "inner".id)
-> Seq Scan on registro r (cost=0.00..28538.85 rows=1397684 width=12)
Filter: (insc_registra_grupo(trayectoria_id, grupo_id, true, false, true) IS NOT NULL)
-> Hash (cost=1159.54..1159.54 rows=2883 width=4)
-> Bitmap Heap Scan on grupo g (cost=31.30..1159.54 rows=2883 width=4)
Recheck Cond: (("año_semestre" = 20071) AND (tipo_id = 'a'::"char"))
-> Bitmap Index Scan on "AsignaturClaveGrupoÚnicaPorAñoSemestre2" (cost=0.00..31.30 rows=2883
width=0)
Index Cond: (("año_semestre" = 20071) AND (tipo_id = 'a'::"char"))
(9 filas)
Using the second plan the query is returning tuples where
año_semestre <> 20071
Any help will be appreciated.
Best regards,
Manuel.