index scan over composite type

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

index scan over composite type

Teodor Sigaev
Hi!

I'm not understand why postgres prefers to sort table instead of using
index only scan when query is a simple inner join on composite type.
Query with equality clause with constant works fine with index scan but
join not. Could somebody point me why? Thank you.

And I'm not able to force merge_join with index scans with any
combination of enable_* variables.

  Attached script is a self-contained test script. Pg config file is
default.

explain
select
     a.idv, b.idv
from
     a, b
where
     a.idv = b.idv;


  Merge Join  (cost=25751.64..27751.64 rows=100000 width=74)
    Merge Cond: (a.idv = b.idv)
    ->  Sort  (cost=12875.82..13125.82 rows=100000 width=37)
          Sort Key: a.idv
          ->  Seq Scan on a  (cost=0.00..1834.00 rows=100000 width=37)
    ->  Sort  (cost=12875.82..13125.82 rows=100000 width=37)
          Sort Key: b.idv
          ->  Seq Scan on b  (cost=0.00..1834.00 rows=100000 width=37)

--
Teodor Sigaev                      E-mail: [hidden email]
                                       WWW: http://www.sigaev.ru/

test.sql (604 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: index scan over composite type

Tom Lane-2
Teodor Sigaev <[hidden email]> writes:
> I'm not understand why postgres prefers to sort table instead of using
> index only scan when query is a simple inner join on composite type.
> Query with equality clause with constant works fine with index scan but
> join not. Could somebody point me why? Thank you.

Hmm ... the reason why not seems to be that canonicalize_ec_expression()
improperly adds a RelabelType node, causing the composite-type Vars to not
be recognized as matching the eclass they should match.  The attached
patch fixes it and doesn't seem to break anything in the regression tests.

This raises the question of why we don't treat type RECORD more like a
true polymorphic type, but that's a can of worms I don't particularly want
to open right now.  For the moment, this is the only IsPolymorphicType
call in the planner AFAICS, so there's some reason to hope that we don't
have more bugs of the same ilk.

                        regards, tom lane


diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 70a925c..e8cdea5 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -497,8 +497,9 @@ canonicalize_ec_expression(Expr *expr, Oid req_type, Oid req_collation)
 
  /*
  * For a polymorphic-input-type opclass, just keep the same exposed type.
+ * RECORD opclasses work like polymorphic types for this purpose.
  */
- if (IsPolymorphicType(req_type))
+ if (IsPolymorphicType(req_type) || req_type == RECORDOID)
  req_type = expr_type;
 
  /*
Reply | Threaded
Open this post in threaded view
|

Re: index scan over composite type

Teodor Sigaev
Thank you. Seems, it works, at least I can't find a counter-example for that.

Tom Lane wrote:

> Teodor Sigaev <[hidden email]> writes:
>> I'm not understand why postgres prefers to sort table instead of using
>> index only scan when query is a simple inner join on composite type.
>> Query with equality clause with constant works fine with index scan but
>> join not. Could somebody point me why? Thank you.
>
> Hmm ... the reason why not seems to be that canonicalize_ec_expression()
> improperly adds a RelabelType node, causing the composite-type Vars to not
> be recognized as matching the eclass they should match.  The attached
> patch fixes it and doesn't seem to break anything in the regression tests.
>
> This raises the question of why we don't treat type RECORD more like a
> true polymorphic type, but that's a can of worms I don't particularly want
> to open right now.  For the moment, this is the only IsPolymorphicType
> call in the planner AFAICS, so there's some reason to hope that we don't
> have more bugs of the same ilk.
>
> regards, tom lane
>

--
Teodor Sigaev                                   E-mail: [hidden email]
                                                    WWW: http://www.sigaev.ru/

Reply | Threaded
Open this post in threaded view
|

Re: index scan over composite type

Tom Lane-2
Teodor Sigaev <[hidden email]> writes:
> Thank you. Seems, it works, at least I can't find a counter-example for that.

Will push, thanks for reviewing.

                        regards, tom lane

Previous Thread Next Thread