Re: TEXT vs VARCHAR join qual push down diffrence, bug or expected?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: TEXT vs VARCHAR join qual push down diffrence, bug or expected?
Date: 2015-09-21 19:01:53
Message-ID: 6068.1442862113@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Jeevan Chalke <jeevan(dot)chalke(at)enterprisedb(dot)com> writes:
> It is observed that, when we have one remote (huge) table and one local
> (small) table and a join between them, then
> 1. If the column type is text, then we push the join qual to the remote
> server, so that we will have less rows to fetch, and thus execution time
> is very less.
> 2. If the column type is varchar, then we do not push the join qual to the
> remote server, resulting into large number of data fetch and thus
> execution time is very high.

Hmm ...

> Also given that RelabelType are just dummy wrapper for binary compatible
> types, can we simply set collation and state from its inner context instead
> on above check block.

I think you're blaming the wrong code; RelabelType is handled basically
the same as most other cases.

It strikes me that this function is really going about things the wrong
way. Rather than trying to determine the output collation per se, what
we ought to be asking is "does every operator in the proposed expression
have an input collation that can be traced to some foreign Var further
down in the expression"? That is, given the example in hand,

RelabelType(ForeignVar) = RelabelType(LocalVar)

the logic ought to be like "the ForeignVar has collation X, and that
bubbles up without change through the RelabelType, and then the equals
operator's inputcollation matches that, so accept it --- regardless of
where the other operand's collation came from exactly". The key point
is that we want to validate operator input collations, not output
collations, as having something to do with what the remote side would do.

This would represent a fairly significant rewrite of foreign_expr_walker's
collation logic; although I think the end result would be no more
complicated, possibly simpler, than it is now.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2015-09-21 19:09:11 Re: COMPARE_POINTER_FIELD been dead 13 years after living 2 weeks
Previous Message Alvaro Herrera 2015-09-21 18:44:49 COMPARE_POINTER_FIELD been dead 13 years after living 2 weeks