Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2AC854764B8 for ; Mon, 20 Jan 2003 17:15:19 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id B0F324765CB for ; Mon, 20 Jan 2003 17:14:41 -0500 (EST) Received: from [63.195.55.98] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 4.0.2) with HTTP id 2320112; Mon, 20 Jan 2003 14:14:46 -0800 From: "Josh Berkus" Subject: Re: 7.3.1 New install, large queries are slow To: "Roman Fail" Cc: X-Mailer: CommuniGate Pro Web Mailer v.4.0.2 Date: Mon, 20 Jan 2003 14:14:46 -0800 Message-ID: In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C0F@pos_pdc.posportal.com> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/210 X-Sequence-Number: 857 Roman, > I'm really surprised that this issue doesn't pop up all the time. As > the community grows, I think it will start to. Actually, in the general sense of intelligent casting, the issue *does* come up all the time. Unfortunately, this is one of those issues that requires both an inspired solution and program-wide overhaul work to fix. In fact, in the FTP achives you can find an alternate version of Postgres (7.1 I think) where someone tried to fix the "stupid casting" issue and succeeded in making Postgres crash and burn instead. > 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!!!). That's great! > 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. So? Send Dave Page (address at pgadmin.postgresql.org) a quick note documenting the problem. I'm sure he'll patch it, or at least fix it for PGAdmin III. > 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. You're welcome! If you can get your boss to authorize it, the Advocacy page (advocacy.postgresql.org) could use some more business testimonials. -Josh Berkus