Re: [PERFORM] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Odd performance results - more info
> Medora Schauer wrote: > > I would greatly appreciate it if someone could run this > code in their > > environment and let me know if you get results similiar to mine. > > The INT test results in execution times of 11 - 50+ secs increasing > > each time the test is run. The FLOAT test execution times are > > consistently < 3 secs regardless of how many times it is run. > > Without actually trying the code, I'd bet that an index is > getting used > for the float8 case, but not in the int8 case: > I've already verifed that the index is used in both cases. Medora ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Odd performance results - more info
> > > Medora Schauer wrote: > > > I would greatly appreciate it if someone could run this > > code in their > > > environment and let me know if you get results similiar to mine. > > > The INT test results in execution times of 11 - 50+ secs > increasing > > > each time the test is run. The FLOAT test execution times are > > > consistently < 3 secs regardless of how many times it is run. > > > > Without actually trying the code, I'd bet that an index is > > getting used > > for the float8 case, but not in the int8 case: > > > > I've already verifed that the index is used in both cases. > I stand corrected. A sequential scan was being used in the INT8 case. When I changed it to INT4 I got better results. I got confused cuz I had changed the types so often. Thanks for your help, Medora ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Odd performance results - more info
Medora Schauer wrote:
I would greatly appreciate it if someone could run this code in their
environment and let me know if you get results similiar to mine.
The INT test results in execution times of 11 - 50+ secs increasing
each time the test is run. The FLOAT test execution times are
consistently < 3 secs regardless of how many times it is run.
Without actually trying the code, I'd bet that an index is getting used
for the float8 case, but not in the int8 case:
if (dtype == INT_TYPE){
snprintf(buffer, sizeof(buffer),
"UPDATE shot_record SET trace_count = %d " \
"WHERE shot_line_num = %d " \
" AND shotpoint = %d " \
" AND index = %d" ,
0, shotline, shotpoint + i, 0);
} else {
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);
}
Try running EXPLAIN ANALYZE on these update statements manually. It also
might help to run VACUUM ANALYZE after populating the tables.
HTH,
Joe
---(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
Re: [PERFORM] Odd performance results
Orignally there were but in the process of trying to figure out what is going on I stripped everything out of the database except the table being queried. > > "Medora Schauer" <[EMAIL PROTECTED]> writes: > > I have a table with a 3 column key. I noticed that when I > update a non-key field > > in a record of the table that the update was taking longer > than I thought it > > should. After much experimenting I discovered that if I > changed the data > > types of two of the key columns to FLOAT8 that I got vastly improved > > performance. > > Are there any foreign key linkages to or from this table? Maybe the > other end of the foreign key is float8? > > regards, tom lane > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Odd performance results
"Medora Schauer" <[EMAIL PROTECTED]> writes: > I have a table with a 3 column key. I noticed that when I update a non-key field > in a record of the table that the update was taking longer than I thought it > should. After much experimenting I discovered that if I changed the data > types of two of the key columns to FLOAT8 that I got vastly improved > performance. Are there any foreign key linkages to or from this table? Maybe the other end of the foreign key is float8? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
