>> It sort of feels like a magic moment. I went back and looked through a
>> lot of the JOIN columns and found that I was mixing int4 with int8 in a
>> lot of them.
>There is note about it in the docs:
>http://www.postgresql.org/idocs/index.php?datatype.html#DATATYPE-INT
>
>I don't know if this is in a faq anywhere, but it should be. I myself have
>helped a number of persons with this. Every once in a while there come
>someone in to the #postgresql irc channel with the exact same problem.
>Usually they leave the channel very happy, when their queries take less
>then a second instead of minutes.
>
>--
>/Dennis
I'm really surprised that this issue doesn't pop up all the time. As the community grows, I think it will start to. I
camevery, very close to dropping PostgreSQL entirely because of it. Hopefully the TODO issue on implicit type casting
willmove closer to the top of the hackers list. But I'm just a beggar so I won't pretend to be a chooser.
Back to my original problems: I re-created everything from scratch and made sure there are no int8's in my entire
database. I found a few more places that I could create useful indexes as well. I didn't get to test it over the
weekend,but today I played with it for several hours and could not get the queries to perform much better than last
week. I was about ready to give up, throw Postgres in the junk pile, and get out the MSSQL CD.
Luckily, an unrelated post on one of the lists mentioned something about ANALYZE, and I realized that I had forgotten
torun it after all the new data was imported (although I did remember a VACUUM FULL). After running ANALYZE, I started
gettingamazing results.....like a query that took 20 minutes last week was taking only 6 milliseconds now. That kicks
theMSSQL server's ass all over the map (as I had originally expected it would!!!).
So things are working pretty good now....and it looks like the whole problem was the data type mismatch issue. I hate
topoint fingers, but the pgAdminII Migration Wizard forces all your primary keys to be int8 even if you set the Type
Mapto int4. The second time through I recognized this and did a pg_dump so I could switch everything to int4. Now I'm
goingto write some minor mods in my Java programs for PGSQL-syntax compatibility, and will hopefully have the
PostgreSQLserver in production shortly.
THANK YOU to everyone on pgsql-performance for all your help. You are the reason that I'll be a long term member of
thePostgres community. I hope that I can assist someone else out in the future.
Roman Fail
Sr. Web Application Developer
POS Portal, Inc.
