Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 45A426503F0 for ; Wed, 6 Aug 2008 18:35:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 75743-05 for ; Wed, 6 Aug 2008 18:34:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.155]) by postgresql.org (Postfix) with ESMTP id 09C976503E9 for ; Wed, 6 Aug 2008 18:35:02 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so108401fgg.23 for ; Wed, 06 Aug 2008 14:35:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type:content-transfer-encoding :content-disposition; bh=U2gXRb8yoNOWHgUd217MxNO+yuL4MZax7LMcedfW7Gg=; b=xfZB9ORPGmpKaJOkvV0BKn1PDObBxJelW9G6hu6FTz126dAJu6aKvc6HYTYjH+abNf ey6dzpZsodU82Hy4DPfKASLowKR5BQQrAMaecNRWjSh8VPRGtRPDzN4pXWbvwfNMbg6u yNerUtphwuyuWXXFL/fNK+zzaXCxS2z4y/org= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type :content-transfer-encoding:content-disposition; b=FDZrET5Anzp5juxI75J15SOh3ZLa0vEgJQmYyHRU3zKiIAlojDSgWgdEJMCxf8vZtW 41cJQOy923c2KlwQzM9rvId090EUxWw/09n0Yja0otmHjeRcSJf55Wp0YOcAotzpuf/M xebu7QatYqRSRc3ZzVlrEs0lq7ji1tgR1Uo28= Received: by 10.86.1.1 with SMTP id 1mr1141080fga.61.1218058501433; Wed, 06 Aug 2008 14:35:01 -0700 (PDT) Received: by 10.86.2.11 with HTTP; Wed, 6 Aug 2008 14:35:01 -0700 (PDT) Message-ID: <84f0acdb0808061435q5ebb607cjc311f1eb4da841a@mail.gmail.com> Date: Wed, 6 Aug 2008 16:35:01 -0500 From: "Joshua Shanks" To: pgsql-performance@postgresql.org Subject: query planner not using the correct index MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/19 X-Sequence-Number: 30900 This query is run on a test system just after a backup of the database has been restored and it does exactly what I expect it to do EXPLAIN ANALYZE SELECT foos.* FROM foos INNER JOIN bars ON foos.id = bars.foos_id WHERE ((bars.bars_id = 12345)) ORDER BY attr1 LIMIT 3 OFFSET 0; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=12946.83..12946.83 rows=3 width=1175) (actual time=0.123..0.131 rows=1 loops=1) -> Sort (cost=12946.83..12950.83 rows=1602 width=1175) (actual time=0.116..0.119 rows=1 loops=1) Sort Key: foos.attr1 -> Nested Loop (cost=28.69..12035.56 rows=1602 width=1175) (actual time=0.071..0.086 rows=1 loops=1) -> Bitmap Heap Scan on bars (cost=28.69..2059.66 rows=1602 width=4) (actual time=0.036..0.039 rows=1 loops=1) Recheck Cond: (bars_id = 12345) -> Bitmap Index Scan on index_bars_on_bars_id (cost=0.00..28.29 rows=1602 width=0) (actual time=0.024..0.024 rows=1 loops=1) Index Cond: (bars_id = 12345) -> Index Scan using foos_pkey on foos (cost=0.00..6.21 rows=1 width=1175) (actual time=0.017..0.021 rows=1 loops=1) Index Cond: (foos.id = bars.foos_id) Total runtime: 0.350 ms This query is run on a production system and is using foos_1attr1 which is an index on attr1 which is a string. EXPLAIN ANALYZE SELECT foos.* FROM foos INNER JOIN bars ON foos.id = bars.foos_id WHERE ((bars.bars_id = 12345)) ORDER BY attr1 LIMIT 3 OFFSET 0; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..2847.31 rows=3 width=332) (actual time=6175.515..6414.599 rows=1 loops=1) -> Nested Loop (cost=0.00..287578.30 rows=303 width=332) (actual time=6175.510..6414.591 rows=1 loops=1) -> Index Scan using foos_1attr1 on foos (cost=0.00..128038.65 rows=1602 width=332) (actual time=0.182..2451.923 rows=2498 loops=1) -> Index Scan using bars_1ix on bars (cost=0.00..0.37 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=421939) Index Cond: (foos.id = bars.foos_id) Filter: (bars_id = 12345) Total runtime: 6414.804 ms