Received: from mail.netuse.de (nuki.netuse.de [193.98.110.1]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA02531 for ; Mon, 17 Jan 2000 15:02:39 -0500 (EST) (envelope-from marten@feki.toppoint.de) Received: from bender.toppoint.de (bender.toppoint.de [195.244.243.2]) by mail.netuse.de (8.9.3+Sun/8.9.3) with ESMTP id VAA09787 for ; Mon, 17 Jan 2000 21:02:35 +0100 (MET) Received: (from uucp@localhost) by bender.toppoint.de (8.9.3/8.9.3) id VAA07715 for pgsql-sql@postgresql.org; Mon, 17 Jan 2000 21:02:32 +0100 (MET) >Received: (from marten@localhost) by feki.toppoint.de (8.9.3/8.9.3/SuSE Linux 8.9.3-0.1) id UAA04218 for pgsql-sql@postgresql.org; Mon, 17 Jan 2000 20:29:34 +0100 From: Marten Feldtmann Message-Id: <200001171929.UAA04218@feki.toppoint.de> Subject: index usage ... strange !? To: pgsql-sql@postgresql.org Date: Mon, 17 Jan 2000 20:29:34 +0100 (CET) X-Mailer: ELM [version 2.4ME+ PL60 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII He's a small example and I'm trying to speed up this query and I can manage it :-( The table: P3AT: AO - char(15), with index AT - Integer, with index AV - VARCHAR(80), with index valind CI - Integer, with index We've about 23000 rows in this table. I would like to execute: SELECT AO,AT,AV FROM P3AT WHERE EXISTS( SELECT AO FROM P3AT WHERE AV='12' AND AT=12 AND CI=17) Explain gives me: Result(946.19,0,0) InitPlan -> INdex Scan using valind on p3at (222,265,12) (fine !) ->Seq Scan on p3at (946,22235,26) (urgghhh ?) This statement takes about 2s to return the results. The select statement within exists just needs 23ms to find the AO value ! SELECT AO FROM P3AT WHERE AO='12' EXPLAIN GIVES ME: INDEX SCAN USING ATROWIND ON P3AT (2,2,12) In general I expect for each unique AO about 10-12 result rows. I've done several vacuum analyze (the first one crashed my database by the way :-(, this tool is really making me crazy - claiming that it can't remove the lock :-(). The reason seems to be the seq scan ... therefore how can I get rid of it ! I use PSQL 6.5.3 under SuSE 6.1. Marten