From: Mathijs Brands <mathijs@ilse.nl>
To: Kevin Quinlan <kquinlan@computer.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Speed of SQL statements
Date: Fri, 23 Feb 2001 15:42:44 +0100
Message-ID: <20010223154244.A74755@ilse.nl> (raw)
In-Reply-To: <000001c099eb$58a5d080$79ac3318@kquinlan>
References: <000001c099eb$58a5d080$79ac3318@kquinlan>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mathijs@ilse.nl, kquinlan@computer.org
Subject: Re: Speed of SQL statements
In-Reply-To: <20010223154244.A74755@ilse.nl>
References: <000001c099eb$58a5d080$79ac3318@kquinlan> <20010223154244.A74755@ilse.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox