Re: Odd performance results - more info

On Tue, 5 Aug 2003, Medora Schauer wrote:

> I hope this piques someones curiosity.  I'd really like to know
> what is going on here...

I think you're getting caught by the typing of constants preventing
index scans.

>              "UPDATE shot_record SET trace_count = %d " \
>              "WHERE shot_line_num = %d " \
>              "  AND shotpoint = %d " \
>              "  AND index = %d" ,
>              0, shotline, shotpoint + i, 0);

I believe that the int constants are going to generally be treated as
int4.  If you're comparing them to an int8 you're not going to get
an index scan probably.  Try explicitly casting the constants to
the appropriate type: CAST(%d AS int8).


>             snprintf(buffer, sizeof(buffer),
>              "UPDATE shot_record SET trace_count = %d " \
>              "WHERE shot_line_num = %f " \
>              "  AND shotpoint = %f " \
>              "  AND index = %d" ,
>              0, (float)shotline, (float)shotpoint + (float)i, 0);

Same general issue here, I think the floats are going to get treated
as float8 in 7.1, so you'll probably need an explicit cast.

As Joe said, try explain on the queries for more details.