Thread

1. Re: [HACKERS] Neverending query on 6.5.2 over Solaris 2.5.1

   Tom Lane <tgl@sss.pgh.pa.us> — 1999-10-22T17:15:09Z

   I wrote:
   > Weird.  I assume that your 'activa' field is 'bool'?  I've been trying
   > to duplicate this misbehavior here, and as near as I can tell the system
   > handles selectivity estimates for boolean fields just fine.  Whatever
   > percentage of 't' values was seen by the last VACUUM ANALYZE is exactly
   > what it uses.
   
   On second thought: 6.5.* can get confused if the column contains more
   NULLs than anything else.  Dunno if you have a lot of nulls in activa,
   but if so you might try changing them all to explicit 'f' and then
   redoing the VACUUM ANALYZE.  Next release will be smarter about keeping
   stats in the presence of many nulls.
   
   It'd be useful to double-check my theory that the system is
   misestimating the selectivity of the WHERE (u.activa) clause.
   You could try this:
   	SELECT count(*) FROM usarios WHERE activa;
   	EXPLAIN SELECT count(*) FROM usarios WHERE activa;
   and see how far off the row count estimate in the EXPLAIN is
   from reality.
   
   			regards, tom lane
   
   

2. Re: [HACKERS] Neverending query on 6.5.2 over Solaris 2.5.1

   Fernando Schapachnik <fpscha@ns1.via-net-works.net.ar> — 1999-10-23T18:29:01Z

   En un mensaje anterior, Tom Lane escribió:
   > I wrote:
   > > Weird.  I assume that your 'activa' field is 'bool'?  I've been trying
   > > to duplicate this misbehavior here, and as near as I can tell the system
   > > handles selectivity estimates for boolean fields just fine.  Whatever
   > > percentage of 't' values was seen by the last VACUUM ANALYZE is exactly
   > > what it uses.
   > 
   > On second thought: 6.5.* can get confused if the column contains more
   > NULLs than anything else.  Dunno if you have a lot of nulls in activa,
   > but if so you might try changing them all to explicit 'f' and then
   > redoing the VACUUM ANALYZE.  Next release will be smarter about keeping
   > stats in the presence of many nulls.
   > 
   > It'd be useful to double-check my theory that the system is
   > misestimating the selectivity of the WHERE (u.activa) clause.
   > You could try this:
   > 	SELECT count(*) FROM usarios WHERE activa;
   
   10571
   
   
   > 	EXPLAIN SELECT count(*) FROM usarios WHERE activa;
   > and see how far off the row count estimate in the EXPLAIN is
   > from reality.
   
   NOTICE:  QUERY PLAN:
   
   Aggregate  (cost=498.84 rows=1 width=4)
     ->  Seq Scan on usuarios  (cost=498.84 rows=1 width=4)
   
   EXPLAIN
   
   Don't hesitate in asking any other info/test you may consider useful.
   
   Regards!
   
   
   
   Fernando P. Schapachnik
   Administración de la red
   VIA Net Works Argentina SA
   Diagonal Roque Sáenz Peña 971, 4º y 5º piso.
   1035 - Capital Federal, Argentina. 
   (54-11) 4323-3333
   http://www.via-net-works.net.ar