Re: Speed of SQL statements

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