Received: from pooh.strategicnetwork.org (IDENT:root@pooh.strategicnetwork.org [159.26.242.70]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA43817 for ; Mon, 6 Mar 2000 10:28:26 -0500 (EST) (envelope-from justinlong@strategicnetwork.org) Received: from JDLONG (mid-tgn-ngd-vty4.as.wcom.net [216.192.75.4]) by pooh.strategicnetwork.org (8.9.3/8.9.3) with SMTP id KAA04598; Mon, 6 Mar 2000 10:26:22 -0500 Received: by localhost with Microsoft MAPI; Mon, 6 Mar 2000 10:27:18 -0500 Message-ID: <01BF8756.8C4F07B0.justinlong@strategicnetwork.org> From: Justin Long Reply-To: "justinlong@strategicnetwork.org" To: "'Peter Eisentraut'" Cc: "'pgsql-sql@postgreSQL.org'" Subject: RE: [SQL] database with 1000000 rows is very slow Date: Mon, 6 Mar 2000 10:27:16 -0500 Organization: Network for Strategic Missions X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Who do we send optimizer questions to? For example, I have a logbook which is ordered by a logtime stamp. Here's my explain output (and by the way, that was very helpful, I hadn't noticed that command before): EXPLAIN select * from logbook order by logtime desc limit 25; NOTICE: QUERY PLAN: Sort (cost=3671.28 rows=76766 width=80) -> Seq Scan on logbook (cost=3671.28 rows=76766 width=80) Now I wonder why it's doing a sequential scan. If it's ordered by logtime and a limit is specified can't it determine that it should just take the bottom 25 entries off the list? _____ Justin Long Network for Strategic Missions 977 Centerville Turnpike, Va Beach, VA 23463 Ofc 757-226-5011, Fax 757-226-5006, Email justinlong@strategicnetwork.org Never retreat. Never surrender. Never cut a deal with a dragon. http://www.strategicnetwork.org -----Original Message----- From: Peter Eisentraut [SMTP:e99re41@DoCS.UU.SE] Sent: Monday, March 06, 2000 2:22 AM To: David Celjuska Cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] database with 1000000 rows is very slow On Sun, 5 Mar 2000, David Celjuska wrote: > CREATE UNIQUE INDEX "article_pkey" on "article" using btree ( "id" > "varchar_ops" ); > this database store. But I think that select * from article where id > like 'something%' is very slow (some minutes) and query as: select * > from article where id='something' is very slow too. I don't know where > is a problem a I would like optimalise this, but how can I do it? If you haven't run vacuum analyze lately then you should do that. > When I use hash except btree, query as: select * from article where > id='something' is fast but select * from article where id='something%' > is very slow. Yup. That's because hashes only work on exact matches and btrees can do ordering (like 'somethink' is surely "larger" than 'something%'). > Or postgresql make indexes automaticly? No, you have to make them, but you did that right. > How can I see that postgres use/or no use index on some query? It is > possible? Yup. EXPLAIN SELECT .... In fact, if you can't make any progress you should always accompany any optimizer issues with the EXPLAIN output. That will help our optimizer gurus. :) -- Peter Eisentraut Sernanders vag 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden ************