X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 38BAF5526B for ; Fri, 4 Mar 2005 16:57:05 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 22937-02 for ; Fri, 4 Mar 2005 16:57:03 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 8585B54F47 for ; Fri, 4 Mar 2005 16:57:02 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j24GuviY003425; (envelope-from ) Fri, 4 Mar 2005 10:56:57 -0600 (CST) Received: from [192.168.1.13] (70-57-223-15.cdrr.qwest.net [70.57.223.15]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j24GusVY007566 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Fri, 4 Mar 2005 10:56:56 -0600 (CST) Message-ID: <42289347.7040607@arbash-meinel.com> Date: Fri, 04 Mar 2005 10:56:39 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ken Cc: Richard Huxton , pgsql-performance@postgresql.org Subject: Re: Help with tuning this query (with explain analyze finally) References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <200503022152.13211.josh@agliodbs.com> <004601c51fbe$828bbb50$6a01a8c0@KEN> <200503030935.14537.josh@agliodbs.com> <001d01c5204a$b440edf0$6a01a8c0@KEN> <4227AA36.2080700@arbash-meinel.com> <000e01c5207a$1f668ac0$6a01a8c0@KEN> <42288529.7000803@archonet.com> <000e01c520d8$4f899a90$780ba8c0@javadude> In-Reply-To: <000e01c520d8$4f899a90$780ba8c0@javadude> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig87B918931C5FE81078B74AC3" X-Virus-Scanned: ClamAV 0.83/723/Thu Feb 24 05:54:24 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200503/75 X-Sequence-Number: 10780 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig87B918931C5FE81078B74AC3 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Ken wrote: > Richard, > > What do you mean by summary table? Basically a cache of the query > into a table with replicated column names of all the joins? I'd > probably have to whipe out the table every minute and re-insert the > data for each carrier in the system. I'm not sure how expensive this > operation would be, but I'm guessing it would be fairly heavy-weight. > And maintaince would be a lot harder because of the duplicated > columns, making refactorings on the database more error-prone. Am I > understanding your suggestion correctly? Please correct me if I am. > >> Can you turn the problem around? Calculate what you want for all >> users (once every 60 seconds) and stuff those results into a summary >> table. Then let the users query the summary table as often as they >> like (with the understanding that the figures aren't going to update >> any faster than once a minute) > It's the same idea of a materialized view, or possibly just a lazy cache. Just try this query: CREATE TABLE cachedview AS select p.id as person_id, s.*, ss.* from shipment s inner join shipment_status ss on s.current_status_id=ss.id inner join release_code rc on ss.release_code_id=rc.id left outer join driver d on s.driver_id=d.id left outer join carrier_code cc on s.carrier_code_id=cc.id where s.carrier_code_id in ( select cc.id from person p inner join carrier_to_person ctp on p.id=ctp.person_id inner join carrier c on ctp.carrier_id=c.id inner join carrier_code cc on cc.carrier_id = c.id ) and s.current_status_id is not null and s.is_purged=false and(rc.number='9' ) and(ss.date>=current_date-31 ) order by ss.date desc ; Notice that I took out the internal p.id = blah. Then you can do: CREATE INDEX cachedview_person_id_idx ON cachedview(person_id); Then from the client side, you can just run: SELECT * from cachedview WHERE person_id = ; Now, this assumes that rc.number='9' is what you always want. If that isn't the case, you could refactor a little bit. This unrolls all of the work, a table which should be really fast to query. If this query takes less than 10s to generate, than just have a service run it every 60s. I think for refreshing, it is actually faster to drop the table and recreate it, rather than deleteing the entries. Dropping also has the advantage that if you ever add more rows to s or ss, then the table automatically gets the new entries. Another possibility, is to have the "cachedview" not use "s.*, ss.*", but instead just include whatever the primary keys are for those tables. Then your final query becomes: SELECT s.*, ss.* FROM cachedview cv, s, ss WHERE cv.person_id = , cv.s_id = s., cv.ss_id = ss.; Again, this should be really fast, because you should have an index on cv.person_id and only have say 300 rows there, and then you are just fetching a few rows from s and ss. You can also use this time to do some of your left joins against other tables. Does this make sense? The biggest advantage you have is your "60s" statement. With that in hand, I think you can do a lot of caching optimizations. John =:-> --------------enig87B918931C5FE81078B74AC3 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCKJNLJdeBCYSNAAMRAhKpAJ0ZW5Kk4l3VfFjhjrmLdMqor7rOXwCgtWMo LRXA/Wtpid9SrV5kjXRSkLg= =UVCE -----END PGP SIGNATURE----- --------------enig87B918931C5FE81078B74AC3--