Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA71004 for ; Mon, 6 Mar 2000 02:22:10 -0500 (EST) (envelope-from e99re41@DoCS.UU.SE) Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160]) by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id IAA10320; Mon, 6 Mar 2000 08:22:06 +0100 (MET) Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE (8.6.12/8.6.12) with ESMTP id IAA17759; Mon, 6 Mar 2000 08:22:05 +0100 X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs Date: Mon, 6 Mar 2000 08:22:05 +0100 (MET) From: Peter Eisentraut Reply-To: Peter Eisentraut To: David Celjuska cc: pgsql-sql@postgresql.org Subject: Re: [SQL] database with 1000000 rows is very slow In-Reply-To: <38C2C1E1.A41501BD@dcsoft.sk> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id CAA71132 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 väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden