Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e6PNUqA58059; Tue, 25 Jul 2000 19:30:52 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id TAA08876; Tue, 25 Jul 2000 19:30:39 -0400 (EDT) To: "Michael Richards" cc: pgsql-sql@postgresql.org, pgsql-bugs@postgresql.org Subject: Re: [SQL] Index selection on a large table In-reply-to: <397E1CCA.000017.47769@frodo.searchcanada.ca> References: <397E1CCA.000017.47769@frodo.searchcanada.ca> Comments: In-reply-to "Michael Richards" message dated "Tue, 25 Jul 2000 19:03:38 -0400" Date: Tue, 25 Jul 2000 19:30:39 -0400 Message-ID: <8873.964567839@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/104 "Michael Richards" writes: > The following query yeilds the questionable query plan: > explain select * from logins where ip='38.30.141.44'; > NOTICE: QUERY PLAN: > Seq Scan on logins (cost=0.00..25248.51 rows=11602 width=48) If there really were 11602 matching rows, the seq scan likely would be the way to go. So the issue here is poor selectivity estimation. You said you did a VACUUM, but you didn't mention if you'd ever done a VACUUM ANALYZE. That looks to me like it's using the default 1% selectivity estimate, which is what you'd get if you hadn't ever done an ANALYZE. If you have done one, what do you get from select attname,attdisbursion,s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid = c.oid and staattnum = attnum and relname = 'logins'; ? regards, tom lane