Received: from serv1.ilse.nl (serv1.ilse.nl [212.189.131.11]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1NEh5x45409 for ; Fri, 23 Feb 2001 09:43:05 -0500 (EST) (envelope-from mathijs@ilse.nl) Received: (from mathijs@localhost) by serv1.ilse.nl (8.11.0/8.10.2) id f1NEgiV75263; Fri, 23 Feb 2001 15:42:44 +0100 (MET) Date: Fri, 23 Feb 2001 15:42:44 +0100 From: Mathijs Brands To: Kevin Quinlan Cc: pgsql-sql@postgresql.org Subject: Re: Speed of SQL statements Message-ID: <20010223154244.A74755@ilse.nl> References: <000001c099eb$58a5d080$79ac3318@kquinlan> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <000001c099eb$58a5d080$79ac3318@kquinlan>; from kquinlan@computer.org on Sun, Feb 18, 2001 at 03:42:43PM -0500 X-Unexpected-Header: The Spanish Inquisition X-Archive-Number: 200102/352 X-Sequence-Number: 1548 On Sun, Feb 18, 2001 at 03:42:43PM -0500, Kevin Quinlan allegedly wrote: > Does anyone have any performance numbers regarding SQL statements, > specifically SELECT, UPDATE, DELETE, and INSERT? For instance, on average > how long does a typical SELECT (UPDATE, DELETE, INSERT) statement take to > execute? You can use the EXPLAIN command to show the execution plan for a query. This allows you to tweak your query (and maybe your indexes) for optimal performance. | iig=# explain select id from entries where not exists (select * from etree where | siteid = id); | NOTICE: QUERY PLAN: | | Seq Scan on entries (cost=0.00..57838.91 rows=1 width=4) | SubPlan | -> Index Scan using idx_etree_siteid on etree (cost=0.00..2.04 rows=1 | width=8) | | EXPLAIN The PostgreSQL documentation has more information on the EXPLAIN command. Make sure you have a look at the VACUUM command, if you haven't already done so. In order to come up with an optimal execution plan pgsql needs information about the contents of your database. This is why you need to run VACUUM ANALYZE from time to time. It also cleans up your indexes. If pgsql is not using indexes when you think it should, run a VACUUM ANALYZE on the table and see if that makes a difference. I hope this helps, Mathijs -- "Books constitute capital." Thomas Jefferson