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, [EMAIL PROTECTED] (Tom Lane) wrote: >Gary Doades <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
