Received: from cherry.napri.sk (cherry.napri.sk [194.1.128.4]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA67791 for ; Sun, 5 Mar 2000 15:22:14 -0500 (EST) (envelope-from dcsoft@dcsoft.sk) Received: from dcsoft.sk (IDENT:root@dial4.napri.sk [194.1.139.4]) by cherry.napri.sk (8.9.3/8.9.3/Debian/GNU) with ESMTP id VAA06319 for ; Sun, 5 Mar 2000 21:22:12 +0100 Sender: root@cherry.napri.sk Message-ID: <38C2C1E1.A41501BD@dcsoft.sk> Date: Sun, 05 Mar 2000 21:21:53 +0100 From: David Celjuska X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: database with 1000000 rows is very slow Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit Hallo All! I have database with follow structure: CREATE TABLE "article" ( "id" character varying(15) NOT NULL, "obj_kod" character varying(15), "popis" character varying(80), "net_price" float4, "our_price" float4, "quantity" int2, "group1" character varying(40) DEFAULT 'ine', "group2" character varying(40), "pic1" character varying(10) DEFAULT 'noname.jpg', "pic2" character varying(10) DEFAULT 'noname.jpg', "alt1" character varying(15), "alt2" character varying(15), "zisk" int2); CREATE UNIQUE INDEX "article_pkey" on "article" using btree ( "id" "varchar_ops" ); and with 1000000 rows. Postgres deamon run on 2xPentiumII 330Mhz with SCSI disk where is 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? 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. Can I index some columns externaly? For example: psql index database table col. Or postgresql make indexes automaticly? How can I see that postgres use/or no use index on some query? It is possible? Thank you every reply, Davy!