Re: Wrong rows selected with view - Mailing list pgsql-general

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=1000width=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@hank.org