Thread: Speed of SQL statements
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? Thank you, Kevin
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
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? Performance is not easily expressed in time, since it depends a lot on the way you've setup your database and your queries (indexes, etc). Of course, the hardware you use also influences the execution time a lot. Cheers, Mathijs -- "It is a great thing to start life with a small number of really good bookswhich are your very own". Sir Arthur ConanDoyle (1859-1930)