Search Postgresql Archives

Re: Wrong rows selected with view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxx


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
       message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux