Thread

1. Why index don't use with SELECT

   Alexander Dederer <dederer@spb.cityline.ru> — 2001-05-16T22:01:21Z

   IMN1=# explain select count(*) from grls WHERE active = 10;
   NOTICE:  QUERY PLAN:
   Aggregate  (cost=993.21..993.21 rows=1 width=0)
     ->  Seq Scan on grls  (cost=0.00..992.44 rows=307 width=0)
    
   IMN1=# explain select count(*) from grls WHERE popularity = 10;
   NOTICE:  QUERY PLAN:
   Aggregate  (cost=23.22..23.22 rows=1 width=0)
     ->  Index Scan using grls_popularity on grls  (cost=0.00..23.17 rows=20 
   width=0)
    
   IMN1=# \di grls
    grls_active         | index | alex
    grls_popularity     | index | alex
   
   Why  SELECT  use Index grls_popularity  and don't use index grls_active ?
   Both create and VACUUM ANALYZE grls?
   
   It's a BUG?
   
   
   

2. Re: Why index don't use with SELECT

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-05-18T16:07:01Z

   How many rows are in grls?  It's estimating 307 matching
   rows in the first case and 20 in the second.  At some
   point due to needing to get the transactional information
   for rows out of the heap, index scan becomes more expensive
   than sequence scan as the number of rows retrieved increases.
   
   This is discussed alot in the mailing lists.  Look at the archives.
   
   On Thu, 17 May 2001, Alexander Dederer wrote:
   
   > IMN1=# explain select count(*) from grls WHERE active = 10;
   > NOTICE:  QUERY PLAN:
   > Aggregate  (cost=993.21..993.21 rows=1 width=0)
   >   ->  Seq Scan on grls  (cost=0.00..992.44 rows=307 width=0)
   >  
   > IMN1=# explain select count(*) from grls WHERE popularity = 10;
   > NOTICE:  QUERY PLAN:
   > Aggregate  (cost=23.22..23.22 rows=1 width=0)
   >   ->  Index Scan using grls_popularity on grls  (cost=0.00..23.17 rows=20 
   > width=0)
   >  
   > IMN1=# \di grls
   >  grls_active         | index | alex
   >  grls_popularity     | index | alex
   > 
   > Why  SELECT  use Index grls_popularity  and don't use index grls_active ?
   > Both create and VACUUM ANALYZE grls?
   > 
   > It's a BUG?
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 2: you can get off all lists at once with the unregister command
   >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   >