pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marten Feldtmann <marten@feki.toppoint.de>
To: pgsql-sql@postgresql.org
Subject: index usage ... strange !?
Date: Mon, 17 Jan 2000 20:29:34 +0100 (CET)
Message-ID: <200001171929.UAA04218@feki.toppoint.de> (raw)

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
 
 





view thread (5+ messages)  latest in thread

Message-ID: <200001171929.UAA04218@feki.toppoint.de>
Permalink:  ../200001171929.UAA04218@feki.toppoint.de/
Also on:    postgresql.org/message-id/200001171929.UAA04218@feki.toppoint.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: marten@feki.toppoint.de
  Subject: Re: index usage ... strange !?
  In-Reply-To: <200001171929.UAA04218@feki.toppoint.de>
  References: <200001171929.UAA04218@feki.toppoint.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox