Re: A question on the query planner - Mailing list pgsql-performance

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Define "no longer works well".  varchar doesn't have its own comparison
> operators anymore, but AFAIK that makes no difference.


Well it seems to completely bar the use of a straight merge join between two
index scans:


test=# set enable_seqscan = off;
SET

test=# explain select * from a,b where a.x=b.x;
                                QUERY PLAN
---------------------------------------------------------------------------
 Nested Loop  (cost=100000000.00..100002188.86 rows=1001 width=64)
   ->  Seq Scan on a  (cost=100000000.00..100000020.00 rows=1000 width=32)
   ->  Index Scan using b_pkey on b  (cost=0.00..2.16 rows=1 width=32)
         Index Cond: (("outer".x)::text = (b.x)::text)
(4 rows)

test=# explain select * from a2,b2 where a2.x=b2.x;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Merge Join  (cost=0.00..63.04 rows=1001 width=64)
   Merge Cond: ("outer".x = "inner".x)
   ->  Index Scan using a2_pkey on a2  (cost=0.00..24.00 rows=1000 width=32)
   ->  Index Scan using b2_pkey on b2  (cost=0.00..24.00 rows=1000 width=32)
(4 rows)


--
greg