"Jason L. Buberel" <jason@buberel.org> writes:
> For reference, when using JasperReports .jrxml files as the basis for
> the query, I only had to do to the following to 'force' postgres to
> treat the jasper report parameter as a number and not text, thereby
> allowing the correct index to be used:
> select * from city summary where city_master_id =
> $P{city_master_id}::bigint ...
> Query times went from 300+ seconds back down to ~100ms.
Hmm ... if Postgres were just given the parameter symbol with no type
information, I believe it would have assumed it was bigint (or in
general, the same type as what it's being compared to). So your
problem suggests that Jasper is deliberately telling the backend that
that parameter is of type text. If that's coming from something you
did in your code, you probably ought to change the code. If not,
it seems like a bug/omission in Jasper.
regards, tom lane
