Re: How to use index in WHERE int = float

On Wed, Nov 05, 2008 at 11:42:12PM +0200, Andrus wrote:
> Scott Marlowe wrote:
> >You do realize that a float is not an exact number. What you and I
> >see as 1228137 might really be, internally, 1228136.9999999999999999 ?
>
> My query contains
> '1228137'::float8
> I do'nt see
> 1228136.9999999999999999
> in this query.

As Scott said, I'd definitely recommend reading about about how floating
point numbers are represented and manipulated. The semantics can get a
bit awkward in the corner cases and having a rough idea of where these
are will help point you in the right direction should strangeness (e.g.
when does 1/3*3 not equal 1) start of occur.

> Those strange casts are auto-generated by ODBC parameter passing software
> which I must use so I must live with it.
>
> Only way to fix this it to replace parameters manually by creating strings
> which I'm trying to do for slow queries.

These last two statements seems mutually exclusive; either use the
"ODBC parameter passing software" or don't. By the sounds of it, I'd
recommend not using it.

> I has feeling that PostgreSql must determine that index is integer type and
> convert float to integer itself to speed it up.

If you build a functional index as in an earlier email from me, it
should just work. Whether it's a good idea for it to work this way is
another matter entirely!

> Another issue:
>
> SELECT dokumnr
> FROM DOK
> where dokumnr IN (123)
> AND ( '0' or
> dokumnr IN (SELECT dokumnr FROM bilkaib WHERE
> alusdok='LG' AND masin LIKE 'a%') )

> If
> '0' or
> is removed form where clause it runs fast since it founds indexes.
> It is real surpise that expression containing
> '0' or
> prevents PostgreSql to use indexes.

Constant propagation is something that PG doesn't do amazingly well at;
I think the main reason is that it's assumed the programmer is capable
of removing most constants and so the optimizer doesn't worry too much
about it.

That said, in 8.3.4, if I run:

EXPLAIN SELECT * FROM source_livestock
WHERE FALSE OR id IN (
SELECT id FROM source_livestock LIMIT 10);

I get:

Seq Scan on source_livestock (cost=0.22..848037.12 rows=18949156 width=58)
Filter: (hashed subplan)
SubPlan
-> Limit (cost=0.00..0.20 rows=10 width=4)
-> Seq Scan on source_livestock (cost=0.00..753291.12 rows=37898312 width=4)

and if I replace the FALSE with TRUE, giving:

EXPLAIN SELECT * FROM source_livestock
WHERE TRUE OR id IN (
SELECT id FROM source_livestock LIMIT 10);

it realizes the subselect isn't needed and I just get:

Seq Scan on source_livestock (cost=0.00..753291.12 rows=37898312 width=58)

so it seems to some propagation is performed, but not all cases are
handled. I don't mind much though; it's a case I know about now and can
deal with.

Sam