It's OK, I'm an idiot.
I read soooo many times that you need to get the data types in the query the same as the column
types or indexes won't work. So I go and get it wrong!
I had defined the parameter to the function as timestamp (equivalent to SQLServer datetime), but the
column data type is date. SQLServer does not have a date type so the datetime parameters work, but
straight conversion causes PG not to use indexes.
I guess that many might be put off PG because of this without bothering to find out why. I think I
read somewhere that there are plans to improve PGs behaviour in this area, is this so?
Cheers,
Gary.
On Sat, 21 Feb 2004 11:15:50 -0500, tgl@sss.pgh.pa.us (Tom Lane) wrote:
>Gary Doades <gpd@cwcom.net> writes:
>> Is there someway to force the use of an index. Or at least get the
>> backend to substitue the parameters in a function before doing the
>> first query plan so it has more typical values to work with?
>
>Could we see the EXPLAIN ANALYZE output for your problem query?
>Table schemas (column data types and available indexes) are necessary
>background for this type of question as well.
>
>You might want to take the question to pgsql-performance, too ...
>it's a tad off topic for -general.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
