Re: 7.3.1 New install, large queries are slow

>> 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 came very, very close to dropping PostgreSQL entirely because of it. Hopefully the TODO issue on implicit type casting will move 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 to run it after all the new data was imported (although I did remember a VACUUM FULL). After running ANALYZE, I started getting amazing results.....like a query that took 20 minutes last week was taking only 6 milliseconds now. That kicks the MSSQL 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 to point fingers, but the pgAdminII Migration Wizard forces all your primary keys to be int8 even if you set the Type Map to int4. The second time through I recognized this and did a pg_dump so I could switch everything to int4. Now I'm going to write some minor mods in my Java programs for PGSQL-syntax compatibility, and will hopefully have the PostgreSQL server 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 the Postgres community. I hope that I can assist someone else out in the future.

Roman Fail
Sr. Web Application Developer
POS Portal, Inc.