Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 23FBC9FB97A for ; Wed, 14 Feb 2007 04:40:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 67826-03 for ; Wed, 14 Feb 2007 04:40:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web52311.mail.yahoo.com (web52311.mail.yahoo.com [206.190.48.154]) by postgresql.org (Postfix) with SMTP id BA11C9FB88D for ; Wed, 14 Feb 2007 04:40:14 -0400 (AST) Received: (qmail 99661 invoked by uid 60001); 14 Feb 2007 08:40:13 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=KAuWqqhXDXntj9Ggvqu/KXXKPIl1sX4Lo9ePNNz7+nyTUEeHudKdHmSVbbbZIyZ37hpSqxgwuryzdAhaF9lYaKDpXnekMJsr82eK7T0fCTLrYeDo3JiLQvz5Yk91FX5r/rHLygL7sA0q29g+y85A8a9h5mWDN183qUQcKw8nrKk=; X-YMail-OSG: TYj6agwVM1kg9NPNdl1f0upcdQYaWYi19EcYkEJaDFe2wPkf7ZtguN4cA7jbwjEZPgTOwhfatmNdi_P3StXFnomTsNlqNhcZQYnpS_PyRodZwFks5mpxLgjFo4NjcPE_UW9OS1clVrwJL7J2_hKf9yd9aQ-- Received: from [202.156.8.12] by web52311.mail.yahoo.com via HTTP; Wed, 14 Feb 2007 00:40:13 PST X-Mailer: YahooMailRC/368.7 YahooMailWebService/0.6.132.7 Date: Wed, 14 Feb 2007 00:40:13 -0800 (PST) From: Brian Herlihy Subject: An unwanted seqscan To: Postgresql Performance MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Message-ID: <379816.98018.qm@web52311.mail.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50 X-Spam-Level: X-Archive-Number: 200702/145 X-Sequence-Number: 23199 Hi,=0A=0AI am having trouble understanding why a seqscan is chosen for this= query.=0A=0AIn practice the seqscan is very expensive, whereas the nested = loop is usually quite fast, even with several hundred rows returned from me= ta_keywords_url.=0A=0AThe server is running version 8.1.3, and both tables = were analyzed recently. meta_keywords contains around 25% dead rows, meta_= keywords_url contains no dead rows.=0A=0AI have included the query written = both as a subquery and as a join.=0A=0AThanks for any assistance!=0ABrian= =0A=0A=0A=0Alive=3D> explain select * from meta_keywords where url_id in (s= elect url_id from meta_keywords_url where host =3D 'postgresql.org');=0A = QUERY PLAN = =0A----------------------------------------------------------------------= -----------------------------------=0A Hash IN Join (cost=3D1755.79..54538= 0.52 rows=3D9442 width=3D29)=0A Hash Cond: ("outer".url_id =3D "inner".ur= l_id)=0A -> Seq Scan on meta_keywords (cost=3D0.00..507976.54 rows=3D71= 10754 width=3D29)=0A -> Hash (cost=3D1754.35..1754.35 rows=3D576 width= =3D4)=0A -> Bitmap Heap Scan on meta_keywords_url (cost=3D11.02..= 1754.35 rows=3D576 width=3D4)=0A Recheck Cond: ((host)::text = =3D 'postgresql.org'::text)=0A -> Bitmap Index Scan on meta_= keywords_url_host_path (cost=3D0.00..11.02 rows=3D576 width=3D0)=0A = Index Cond: ((host)::text =3D 'postgresql.org'::text)=0A(8 ro= ws)=0A=0Alive=3D> set enable_seqscan=3Doff;=0ASET=0Alive=3D> explain select= * from meta_keywords where url_id in (select url_id from meta_keywords_url= where host =3D 'postgresql.org');=0A = QUERY PLAN =0A------------------------------= ---------------------------------------------------------------------------= =0A Nested Loop (cost=3D1755.79..3161748.83 rows=3D9442 width=3D29)=0A -= > HashAggregate (cost=3D1755.79..1761.55 rows=3D576 width=3D4)=0A = -> Bitmap Heap Scan on meta_keywords_url (cost=3D11.02..1754.35 rows=3D5= 76 width=3D4)=0A Recheck Cond: ((host)::text =3D 'postgresql.= org'::text)=0A -> Bitmap Index Scan on meta_keywords_url_hos= t_path (cost=3D0.00..11.02 rows=3D576 width=3D0)=0A In= dex Cond: ((host)::text =3D 'postgresql.org'::text)=0A -> Index Scan usi= ng meta_keywords_url_id on meta_keywords (cost=3D0.00..5453.28 rows=3D2625= width=3D29)=0A Index Cond: (meta_keywords.url_id =3D "outer".url_i= d)=0A(8 rows)=0A=0Alive=3D> explain select * from meta_keywords join meta_k= eywords_url using (url_id) where host =3D 'postgresql.org'; = = QUERY PLAN =0A-------------------------------------= --------------------------------------------------------------------=0A Has= h Join (cost=3D1758.52..543685.43 rows=3D9297 width=3D107)=0A Hash Cond:= ("outer".url_id =3D "inner".url_id)=0A -> Seq Scan on meta_keywords (c= ost=3D0.00..506859.29 rows=3D6994929 width=3D28)=0A -> Hash (cost=3D175= 7.08..1757.08 rows=3D577 width=3D83)=0A -> Bitmap Heap Scan on met= a_keywords_url (cost=3D11.02..1757.08 rows=3D577 width=3D83)=0A = Recheck Cond: ((host)::text =3D 'postgresql.org'::text)=0A = -> Bitmap Index Scan on meta_keywords_url_host_path (cost=3D0.00..11.02= rows=3D577 width=3D0)=0A Index Cond: ((host)::text =3D= 'postgresql.org'::text)=0A(8 rows)=0A=0Alive=3D> set enable_seqscan=3Doff;= =0ASET=0Alive=3D> explain select * from meta_keywords join meta_keywords_ur= l using (url_id) where host =3D 'postgresql.org';=0A = QUERY PLAN =0A---------------= ---------------------------------------------------------------------------= -----------------------=0A Nested Loop (cost=3D0.00..3348211.21 rows=3D929= 7 width=3D107)=0A -> Index Scan using meta_keywords_url_host_path on met= a_keywords_url (cost=3D0.00..2230.24 rows=3D577 width=3D83)=0A Ind= ex Cond: ((host)::text =3D 'postgresql.org'::text)=0A -> Index Scan usin= g meta_keywords_url_id on meta_keywords (cost=3D0.00..5765.81 rows=3D2649 = width=3D28)=0A Index Cond: (meta_keywords.url_id =3D "outer".url_id= )=0A(5 rows)=0A=0A=0A=0A