Hi Tore,
the thing is - without RTRIM "equal" comparison will fail in most cases
since all CHAR columns are padded with spaces to their max width. And
yes, with RTRIM index will most likely be ignored. Define the column as
VARCHAR, and RTRIM won't be used.
Andrus
On Friday, September 5, 2003, at 08:57 AM, Tore Halset wrote:
> Hello!
>
> I have a table cell with a primary key named cell_id. The database is
> PostgreSQL. cell_id is char(8).
>
> The datamap:
> <db-entity name="cell" schema="public">
> <db-attribute name="cell_id"
> type="CHAR"
> isPrimaryKey="true"
> isMandatory="true"
> length="8"/>
>
> I am using the follwing code to query for one specific cell
> ExpressionFactory.binaryPathExp(Expression.EQUAL_TO, "cellId", cellId);
> SelectQuery query = new SelectQuery(Cell.class, qual);
> List cells = getDataContext().performQuery(query);
>
> The generated SQL performs a rtrim
> RTRIM(cell_id) = ?
>
> I guess the rtrim-code are not needed? Will PostgreSQL still be able to
> use the index?
>
> => explain select * from cell where cell_id='ABCDEFGH';
> QUERY PLAN
> -----------------------------------------------------------------------
> Index Scan using cell_pkey on cell (cost=0.00..5.89 rows=1 width=86)
> Index Cond: (cell_id = 'ABCDEFGH'::bpchar)
> (2 rows)
>
> => explain select * from cell where rtrim(cell_id)='ABCDEFGH';
> QUERY PLAN
> --------------------------------------------------------
> Seq Scan on cell (cost=0.00..110.02 rows=18 width=86)
> Filter: (rtrim((cell_id)::text) = 'ABCDEFGH'::text)
> (2 rows)
>
> Regards,
> - Tore.
>
This archive was generated by hypermail 2.0.0 : Fri Sep 05 2003 - 09:15:21 EDT