pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marten Feldtmann <marten@feki.toppoint.de>
To: Marten Feldtmann <marten@feki.toppoint.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] index usage ... strange !?
Date: Wed, 19 Jan 2000 18:46:26 +0100 (CET)
Message-ID: <200001191746.SAA09643@feki.toppoint.de> (raw)
In-Reply-To: <200001180653.HAA05729@feki.toppoint.de>

> 
>  Therefore I have to rewrite the sql statement above to:
> 
>  SELECT P3AT.AV,P3AT.AT,P3AT.AV FROM P3AT  WHERE
>  EXISTS( SELECT B.AO FROM P3AT as B WHERE B.AO=P3AT.AO AND ..... )
> 

 Ok, indeed it was the wrong statement, therefore I rewrote it to:

 SELECT P3AT.AO,P3AT.AT,P3AT.AV FROM P3AT
 WHERE
   EXISTS
     (SELECT B.AO FROM P3AT AS B WHERE B.AO=P3AT.AO AND ....)

 There're indices on AO,AT,AV. Size of table about 23000 rows.

 He uses indices only for the statement within EXISTS, which seems
to be pretty fast but for the outer SELECT statement he uses a
sequential scan ... which brings the the statement down from 23 ms to
2000ms.

 Actually I don not understand it. The "B.AO=P3AT.AO" should create
internally a join (?) and therefore he could use the index on AO in
the outer select to create the result - but the seq scan seems to
be wrong.

 Marten






view thread (5+ messages)  latest in thread

Message-ID: <200001191746.SAA09643@feki.toppoint.de>
Permalink:  ../200001191746.SAA09643@feki.toppoint.de/
Also on:    postgresql.org/message-id/200001191746.SAA09643@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, tgl@sss.pgh.pa.us
  Subject: Re: [SQL] index usage ... strange !?
  In-Reply-To: <200001191746.SAA09643@feki.toppoint.de>
  References: <200001180653.HAA05729@feki.toppoint.de> <200001191746.SAA09643@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