X-Original-To: pgsql-general@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 974AC474E4F for ; Thu, 13 Feb 2003 12:39:07 -0500 (EST) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id 01D23474E42 for ; Thu, 13 Feb 2003 12:39:07 -0500 (EST) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id BBFD5138096; Thu, 13 Feb 2003 12:39:11 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 18jNKI-000763-00; Thu, 13 Feb 2003 12:39:10 -0500 To: pgsql-general@postgresql.org Subject: index scan with index cond on first column doesn't recognize sort order of second column From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 13 Feb 2003 12:39:09 -0500 Message-ID: <87el6ckrlu.fsf@stark.dyndns.tv> Lines: 38 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/723 X-Sequence-Number: 37477 Here's a corner case where the optimizer is doing a redundant sort. I'm not sure if I'm doing something wrong or if it's just something the optimizer doesn't notice. The second index scan, the one on cache_foo, is on a two-column index. Since it has an Index Cond on the first column, it's effectively scanning in the order of the second column. That second column is precisely the join condition, so it could do a merge join without an extra sort. It's actually doing the merge join but it's doing a useless sort first. db=> explain analyze select * from foo_bar join cache_foo using (foo_id) where key_id = 839; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=4053.86..5060.00 rows=2641 width=32) (actual time=111.47..562.41 rows=8640 loops=1) Merge Cond: ("outer".foo_id = "inner".foo_id) -> Index Scan using idx_foo_bar_foo on foo_bar (cost=0.00..853.34 rows=45288 width=8) (actual time=0.03..239.75 rows=45140 loops=1) -> Sort (cost=4053.86..4060.46 rows=2640 width=24) (actual time=111.37..121.70 rows=8641 loops=1) Sort Key: cache_foo.foo_id -> Index Scan using idx_cache_foo_foo on cache_foo (cost=0.00..3903.82 rows=2640 width=24) (actual time=0.05..47.48 rows=8666 loops=1) Index Cond: (key_id = 839) Total runtime: 577.10 msec (8 rows) Time: 580.41 ms db=> \d cache_foo Table "public.cache_foo" Column | Type | Modifiers -------------------+------------------+----------- key_id | integer | foo_id | integer | Indexes: idx_cache_foo_foo btree (key_id, foo_id) [Sorry, but I have to search+replace on the names at the client's request] -- greg