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 347345372C for ; Wed, 20 Apr 2005 15:50:25 -0300 (ADT) 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 08752-04 for ; Wed, 20 Apr 2005 18:50:20 +0000 (GMT) Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id B499B53DB5 for ; Wed, 20 Apr 2005 14:13:41 -0300 (ADT) Received: from terminus.dnttm.ro (terminus.dnttm.ro [193.226.98.11]) by svr2.postgresql.org (Postfix) with ESMTP id 4A8A7F11A1 for ; Wed, 20 Apr 2005 18:03:11 +0100 (BST) Received: from nutzi (dnt-gw-softnrg.dnttm.ro [193.226.89.168]) by terminus.dnttm.ro (8.13.3/8.12.9) with SMTP id j3KH4xUd005370; Wed, 20 Apr 2005 20:05:00 +0300 Received: by nutzi (VPOP3 - Unregistered) with SMTP; Wed, 20 Apr 2005 20:10:41 +0300 Received: from 127.0.0.1 (AVG SMTP 7.0.308 [266.9.18]); Wed, 20 Apr 2005 20:10:42 +0300 Message-ID: <42668D12.1060701@softnrg.dnttm.ro> Date: Wed, 20 Apr 2005 20:10:42 +0300 From: Andrei Gaspar User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en To: Michael Fuhr Cc: pgsql-performance@postgresql.org Subject: Re: Sort and index References: <49E94D0CFCD4DB43AFBA928DDD20C8F9026184B6@asg002.asg.local> <20050418171013.GA34421@winnie.fuhr.org> In-Reply-To: <20050418171013.GA34421@winnie.fuhr.org> X-Enigmail-Version: 0.90.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Mime-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=ISO-8859-1; format=flowed X-Server: VPOP3 V1.2.0d Evaluation X-Virus-Scanned: ClamAV 0.80/842/Wed Apr 20 00:39:01 2005 clamav-milter version 0.80j on localhost 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: 200504/521 X-Sequence-Number: 11918 Michael Fuhr wrote: >On Mon, Apr 18, 2005 at 10:44:43AM -0500, Dave Held wrote: > > >>>I thought that an index can be used for sorting. >>>I'm a little confused about the following result: >>> >>>create index OperationsName on Operations(cOperationName); >>>explain SELECT * FROM Operations ORDER BY cOperationName; >>> QUERY PLAN >>>-------------------------------------------------------------- >>>--------- >>> Sort (cost=185.37..189.20 rows=1532 width=498) >>> Sort Key: coperationname >>> -> Seq Scan on operations (cost=0.00..104.32 rows=1532 width=498) >>>(3 rows) >>> >>>Is this supposed to be so? >>> >>> >>Since you are fetching the entire table, you are touching all the rows. >>If the query were to fetch the rows in index order, it would be seeking >>all over the table's tracks. By fetching in sequence order, it has a >>much better chance of fetching rows in a way that minimizes head seeks. >>Since disk I/O is generally 10-100x slower than RAM, the in-memory sort >>can be surprisingly slow and still beat indexed disk access. Of course, >>this is only true if the table can fit and be sorted entirely in memory >>(which, with 1500 rows, probably can). >> >> > >Out of curiosity, what are the results of the following queries? >(Queries run twice to make sure time differences aren't due to >caching.) > >SET enable_seqscan TO on; >SET enable_indexscan TO off; >EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; >EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; > >SET enable_seqscan TO off; >SET enable_indexscan TO on; >EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; >EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; > >SELECT version(); > >With 1500 rows of random data, I consistently see better performance >with an index scan (about twice as fast as a sequence scan), and >the planner uses an index scan if it has a choice (i.e., when >enable_seqscan and enable_indexscan are both on). But my test case >and postgresql.conf settings might be different enough from yours >to account for different behavior. > > > Here is the output from the statements above. I know the times seem too small to care, but what triggered my question is the fact that in the logs there are a lot of lines like (i replaced the list of 43 fields with *). I use ODBC (8.0.1.1) and to change the application to cache the table isn't feasible. 2005-04-19 10:07:05 LOG: duration: 937.000 ms statement: PREPARE "_PLAN35b0068" as SELECT * FROM Operations ORDER BY cOperationName;EXECUTE "_PLAN35b0068" 2005-04-19 10:07:09 LOG: duration: 1344.000 ms statement: PREPARE "_PLAN35b0068" as SELECT * FROM Operations ORDER BY cOperationName;EXECUTE "_PLAN35b0068" 2005-04-19 10:07:15 LOG: duration: 1031.000 ms statement: PREPARE "_PLAN35b0068" as SELECT * FROM Operations ORDER BY cOperationName;EXECUTE "_PLAN35b0068" 2005-04-19 10:07:19 LOG: duration: 734.000 ms statement: PREPARE "_PLAN35b0068" as SELECT * FROM Operations ORDER BY cOperationName;EXECUTE "_PLAN35b0068" The times reported by explain analyze are so small though, the intervals reported in pg_log are more real, tkp=# SET enable_seqscan TO on; SET tkp=# SET enable_indexscan TO off; SET tkp=# EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Sort (cost=185.37..189.20 rows=1532 width=498) (actual time=235.000..235.000 rows=1532 loops=1) Sort Key: coperationname -> Seq Scan on operations (cost=0.00..104.32 rows=1532 width=498) (actual time=0.000..124.000 rows=1532 loops=1) Total runtime: 267.000 ms (4 rows) tkp=# EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Sort (cost=185.37..189.20 rows=1532 width=498) (actual time=16.000..16.000 rows=1532 loops=1) Sort Key: coperationname -> Seq Scan on operations (cost=0.00..104.32 rows=1532 width=498) (actual time=0.000..0.000 rows=1532 loops=1) Total runtime: 31.000 ms (4 rows) tkp=# tkp=# SET enable_seqscan TO off; SET tkp=# SET enable_indexscan TO on; SET tkp=# EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Index Scan using operationsname on operations (cost=0.00..350.01 rows=1532 width=498) (actual time=16.000..62.000 rows=1532 loops=1) Total runtime: 62.000 ms (2 rows) tkp=# EXPLAIN ANALYZE SELECT * FROM Operations ORDER BY cOperationName; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Index Scan using operationsname on operations (cost=0.00..350.01 rows=1532 width=498) (actual time=0.000..16.000 rows=1532 loops=1) Total runtime: 16.000 ms (2 rows) tkp=# tkp=# SELECT version(); version ------------------------------------------------------------------------------------------ PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) (1 row) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.18 - Release Date: 4/19/2005