Re: Wrong rows selected with view

On Wed, Nov 16, 2005 at 03:42:19PM +0100, Andreas Seltenreich wrote:
> How does the query plan change when you make those changes? If it only
> occurs if a certain index is used, it might be corrupt (=> REINDEX).

I did a "reindex database ws2;" and no change.

I'm not very good at reading the query plans. For one thing, they
always send me off on some tangent wondering why it's doing a Seq Scan
instead of a index scan. ;)

The first plan below returns the correct number of rows, the second plan does
not. These are after I did the reindex, btw.

ws2=> explain select id from bar where person_id = 84;

Subquery Scan bar (cost=1225.81..1243.32 rows=6 width=4)
Filter: (person_id = 84)
-> Unique (cost=1225.81..1230.82 rows=1000 width=334)
-> Sort (cost=1225.81..1228.31 rows=1003 width=334)
Sort Key: "class".id
-> Hash Join (cost=802.15..1175.81 rows=1003 width=334)
Hash Cond: ("outer".person = "inner".id)
-> Hash Join (cost=67.50..203.81 rows=1003 width=315)
Hash Cond: ("outer".region = "inner".id)
-> Hash Join (cost=45.00..163.77 rows=1002 width=279)
Hash Cond: ("outer"."location" = "inner".id)
-> Hash Join (cost=22.50..118.74 rows=1001 width=141)
Hash Cond: ("outer"."class" = "inner".id)
-> Seq Scan on instructors (cost=0.00..20.00 rows=1000 width=8)
-> Hash (cost=20.00..20.00 rows=1000 width=137)
-> Seq Scan on "class" (cost=0.00..20.00 rows=1000 width=137)
-> Hash (cost=20.00..20.00 rows=1000 width=142)
-> Seq Scan on "location" (cost=0.00..20.00 rows=1000 width=142)
-> Hash (cost=20.00..20.00 rows=1000 width=40)
-> Seq Scan on region (cost=0.00..20.00 rows=1000 width=40)
-> Hash (cost=649.12..649.12 rows=10212 width=23)
-> Seq Scan on person (cost=0.00..649.12 rows=10212 width=23)
(22 rows)

This returns one row less and the only change to the view is this
commented out column:

-- class.full_message AS full_message, -- this

ws2=> explain select id from bar where person_id = 84;

Subquery Scan bar (cost=1222.54..1240.05 rows=6 width=4)
Filter: (person_id = 84)
-> Unique (cost=1222.54..1227.55 rows=1000 width=366)
-> Sort (cost=1222.54..1225.05 rows=1003 width=366)
Sort Key: "class".id
-> Hash Join (cost=779.65..1172.54 rows=1003 width=366)
Hash Cond: ("outer".person = "inner".id)
-> Hash Join (cost=45.00..204.14 rows=1003 width=347)
Hash Cond: ("outer".region = "inner".id)
-> Hash Join (cost=22.50..164.10 rows=1002 width=311)
Hash Cond: ("outer"."location" = "inner".id)
-> Merge Join (cost=0.00..119.06 rows=1001 width=173)
Merge Cond: ("outer".id = "inner"."class")
-> Index Scan using class_pkey on "class" (cost=0.00..52.00 rows=1000 width=169)
-> Index Scan using instructors_class_index on instructors (cost=0.00..52.00 rows=1000 width=8)
-> Hash (cost=20.00..20.00 rows=1000 width=142)
-> Seq Scan on "location" (cost=0.00..20.00 rows=1000 width=142)
-> Hash (cost=20.00..20.00 rows=1000 width=40)
-> Seq Scan on region (cost=0.00..20.00 rows=1000 width=40)
-> Hash (cost=649.12..649.12 rows=10212 width=23)
-> Seq Scan on person (cost=0.00..649.12 rows=10212 width=23)
(21 rows)

--
Bill Moseley
moseley(at)hank(dot)org