From: Greg Stark <gsstark@mit.edu>
To: pgsql-general@postgresql.org
Subject: index scan with index cond on first column doesn't recognize sort order of second column
Date: 13 Feb 2003 12:39:09 -0500
Message-ID: <87el6ckrlu.fsf@stark.dyndns.tv> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: gsstark@mit.edu
Subject: Re: index scan with index cond on first column doesn't recognize sort order of second column
In-Reply-To: <87el6ckrlu.fsf@stark.dyndns.tv>
References: <87el6ckrlu.fsf@stark.dyndns.tv>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox