Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 31CBA9FB646 for ; Fri, 6 Apr 2007 14:43:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 31305-05 for ; Fri, 6 Apr 2007 14:42:55 -0300 (ADT) X-Greylist: delayed 00:21:02.123001 by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 51EB39FBE44 for ; Fri, 6 Apr 2007 14:42:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from thebsh.sygroup.ch (thebsh.sygroup.ch [193.219.115.146]) by svr4.postgresql.org (Postfix) with ESMTP id C234F5AF881 for ; Fri, 6 Apr 2007 17:21:49 +0000 (GMT) Received: by thebsh.sygroup.ch (Postfix, from userid 1000) id E801A79B27; Fri, 6 Apr 2007 19:21:39 +0200 (CEST) Date: Fri, 6 Apr 2007 19:21:39 +0200 From: Tonnerre LOMBARD To: pgsql-interfaces@postgresql.org Subject: Index "misbehavior" in PostgreSQL 8.2.2? Message-ID: <20070406172139.GA4527@thebsh.sygroup-int.ch> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="tKW2IUtsqtDRztdT" Content-Disposition: inline X-GPG-KeyID: 0xA7FD39D6 X-GPG-Fingerprint: 751E 5CEE 425F 70EC C65E 9C2E ED66 3066 A7FD 39D6 X-GPG-URL: http://thebsh.sygroup.ch/~tonnerre/tonnerre.asc User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200704/6 X-Sequence-Number: 6494 --tKW2IUtsqtDRztdT Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Salut, I have four tables, two of which are relevant for this: searchengine=3D# \d websites Table "public.websites" Column | Type | Modifiers = =20 ------------+--------------------------+-----------------------------------= -------------------- id | bigint | not null default nextval('websites= _id_seq'::regclass) url | text | not null title | text |=20 abstract | text |=20 lastindex | timestamp with time zone |=20 authority | bigint | not null default 0 failed | boolean | not null default false spamminess | smallint | not null default 0 Indexes: "websites_pkey" PRIMARY KEY, btree (id) "websites_url_key" UNIQUE, btree (url) "websites_authority_key" btree (authority) "websites_failed_key" btree (failed) "websites_lastindex_key" btree (lastindex) "websites_spamminess_key" btree (spamminess) searchengine=3D# SELECT pg_size_pretty (pg_relation_size ('websites')); pg_size_pretty=20 ---------------- 293 MB (1 row) searchengine=3D# SELECT COUNT(*) FROM websites; count -------- 828778 (1 row) searchengine=3D# \d sitewords Table "public.sitewords" Column | Type | Modifiers=20 ------------+--------+----------- id_site | bigint | not null id_keyword | bigint | not null count | bigint | not null ratio | bigint |=20 Indexes: "sitewords_key" UNIQUE, btree (id_site, id_keyword) "sitewords_id_keyword_key" btree (id_keyword) "sitewords_id_site_key" btree (id_site) searchengine=3D# SELECT COUNT(*) FROM sitewords; count ---------- 46696260 (1 row) searchengine=3D# SELECT pg_size_pretty (pg_relation_size ('sitewords')); pg_size_pretty=20 ---------------- 3041 MB (1 row) searchengine=3D#=20 My effective_cache_size is set to 768MB. I am trying to run a query: searchengine=3D# EXPLAIN ANALYZE SELECT COUNT(me.id_keyword) * SUM(ratio) A= S relevance, id_site FROM sitewords me JOIN websites ws ON me.id_site =3D w= s.id WHERE me.id_keyword IN (4241,28303) GROUP BY id_site ORDER BY relevanc= e DESC LIMIT 10; = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------ Limit (cost=3D150319.90..150319.91 rows=3D3 width=3D24) (actual time=3D92= 11.903..9212.013 rows=3D10 loops=3D1) -> Sort (cost=3D150319.90..150319.91 rows=3D3 width=3D24) (actual time= =3D9211.895..9211.931 rows=3D10 loops=3D1) Sort Key: ((count(me.id_keyword))::numeric * sum(me.ratio)) -> HashAggregate (cost=3D150319.81..150319.87 rows=3D3 width=3D2= 4) (actual time=3D9180.744..9197.080 rows=3D3320 loops=3D1) -> Hash Join (cost=3D60470.11..150116.47 rows=3D27112 widt= h=3D24) (actual time=3D7816.860..9151.601 rows=3D5747 loops=3D1) Hash Cond: (me.id_site =3D ws.id) -> Bitmap Heap Scan on sitewords me (cost=3D609.31..= 85356.43 rows=3D27112 width=3D24) (actual time=3D3.245..137.026 rows=3D5748= loops=3D1) Recheck Cond: (id_keyword =3D ANY ('{4241,28303}= '::bigint[])) -> Bitmap Index Scan on sitewords_id_keyword_ke= y (cost=3D0.00..602.53 rows=3D27112 width=3D0) (actual time=3D2.169..2.169= rows=3D5748 loops=3D1) Index Cond: (id_keyword =3D ANY ('{4241,28= 303}'::bigint[])) -> Hash (cost=3D45674.80..45674.80 rows=3D816080 wid= th=3D8) (actual time=3D7786.940..7786.940 rows=3D829078 loops=3D1) -> Seq Scan on websites ws (cost=3D0.00..45674= =2E80 rows=3D816080 width=3D8) (actual time=3D0.026..4126.069 rows=3D829078= loops=3D1) Total runtime: 9212.679 ms (13 rows) searchengine=3D#=20 The only way to get PostgreSQL to use the index appears to be to set the random_page_cost to 1.0 (1.1 also doesn't work). In that case, I get: searchengine=3D# EXPLAIN ANALYZE SELECT COUNT(me.id_keyword) * SUM(ratio) A= S relevance, id_site FROM sitewords me JOIN websites ws ON me.id_site =3D w= s.id WHERE me.id_keyword IN (4241,28303) GROUP BY id_site ORDER BY relevanc= e DESC LIMIT 10; = QUERY PLAN = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------ Limit (cost=3D78647.65..78647.66 rows=3D3 width=3D24) (actual time=3D418.= 368..418.478 rows=3D10 loops=3D1) -> Sort (cost=3D78647.65..78647.66 rows=3D3 width=3D24) (actual time= =3D418.359..418.395 rows=3D10 loops=3D1) Sort Key: ((count(me.id_keyword))::numeric * sum(me.ratio)) -> HashAggregate (cost=3D78647.57..78647.63 rows=3D3 width=3D24)= (actual time=3D386.734..403.241 rows=3D3320 loops=3D1) -> Nested Loop (cost=3D310.29..78444.23 rows=3D27112 width= =3D24) (actual time=3D2.878..354.418 rows=3D5747 loops=3D1) -> Bitmap Heap Scan on sitewords me (cost=3D310.29..= 26849.19 rows=3D27112 width=3D24) (actual time=3D2.789..135.915 rows=3D5748= loops=3D1) Recheck Cond: (id_keyword =3D ANY ('{4241,28303}= '::bigint[])) -> Bitmap Index Scan on sitewords_id_keyword_ke= y (cost=3D0.00..303.51 rows=3D27112 width=3D0) (actual time=3D1.762..1.762= rows=3D5748 loops=3D1) Index Cond: (id_keyword =3D ANY ('{4241,28= 303}'::bigint[])) -> Index Scan using websites_pkey on websites ws (co= st=3D0.00..1.89 rows=3D1 width=3D8) (actual time=3D0.017..0.022 rows=3D1 lo= ops=3D5748) Index Cond: (me.id_site =3D ws.id) Total runtime: 418.791 ms (12 rows) searchengine=3D#=20 which is fine. However, if I leave random_page_cost at 4 and do the followi= ng query: searchengine=3D# EXPLAIN ANALYZE select x.ratio * x.count, x.id_site from (= SELECT me.id_site, count(me.id_site), sum(me.ratio) as ratio FROM sitewords= me WHERE me.id_keyword IN (4241, 28303) group by id_site) x join websites = ws on (ws.id =3D x.id_site); QUERY= PLAN = =20 ---------------------------------------------------------------------------= --------------------------------------------------------------------------- Nested Loop (cost=3D85559.77..85585.13 rows=3D3 width=3D48) (actual time= =3D169.707..337.717 rows=3D3320 loops=3D1) -> HashAggregate (cost=3D85559.77..85559.82 rows=3D3 width=3D16) (actu= al time=3D169.567..184.975 rows=3D3321 loops=3D1) -> Bitmap Heap Scan on sitewords me (cost=3D609.31..85356.43 row= s=3D27112 width=3D16) (actual time=3D2.897..137.590 rows=3D5748 loops=3D1) Recheck Cond: (id_keyword =3D ANY ('{4241,28303}'::bigint[])) -> Bitmap Index Scan on sitewords_id_keyword_key (cost=3D0= =2E00..602.53 rows=3D27112 width=3D0) (actual time=3D1.820..1.820 rows=3D57= 48 loops=3D1) Index Cond: (id_keyword =3D ANY ('{4241,28303}'::bigin= t[])) -> Index Scan using websites_pkey on websites ws (cost=3D0.00..8.41 ro= ws=3D1 width=3D8) (actual time=3D0.025..0.029 rows=3D1 loops=3D3321) Index Cond: (ws.id =3D x.id_site) Total runtime: 351.053 ms (9 rows) searchengine=3D#=20 the result is even more acceptable than with the above JOIN statement. The tables are analyzed, and I have vacuumed regularly. Any idea why PostgreSQL attempts a full sequential scan here? Tonnerre --tKW2IUtsqtDRztdT Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (NetBSD) iQIVAwUBRhaBo+1mMGan/TnWAQJWCQ//fVJ5+E/cV28nU2BXXeNj4MslMcr+oSJu hW2rCG3KVYcRhjyl+64QrEPoeh/ThcRLdVQP0syuiGVR8y46zOtOE5s4UPbfO5F0 1zZnN0dAKLBUw0rStM9IAygq5+5GCT/7CG7lfG+m6K/XZ3HPewUTktD42wqCEGLj CGpsWUB5sLH0Co9u+tmpZedsBqh5c6TdtHxuekVw4Jn+2Nxbk8kY95q6X/7UpS23 qZQX2JC5v9c7/xLs10gGTtIHqaLegKSpoFJKle7afM5RQdpJt8DOLBYoAqJfq9jE 4pc6KeevXrpNyN+neL78BdpMeA3HR5obJv89/AivJ4b6+rzTldvHDKKm9fRXJ3x6 tS0IOBCy11MXVFopMKRrVQLcTdrxtGVu5Z6pQvEcKQQMkXpWXgqmDdot3saTZkB3 mT4bMYtqX6M6vSdLlPgQd+L2zQyeLIjhpxxwK9tr2zD09BnjVTh4d/mO0fjmmIjK f9QEGzKegZuDSJMXEBwkr7jW9SGRKhvIx42+FYp5rhLhE33Nr9uAPN3uMoCBZRS8 xiYk0MsMVMgYbna8xXLSW0ZicvmfvaHVWoFIEitQPcIiEXPZLzR15uxM+J1TbFoL mqbNRTC9hv63I4rj7SA70Dcn1o+oSvUwNCt/0PeEVDCzY2vrPDAQinRXCBHf73af QT9tkBga3xo= =6KWn -----END PGP SIGNATURE----- --tKW2IUtsqtDRztdT--