Skip site navigation (1) Skip section navigation (2)

Re: [SQL] index usage ... strange !?

From: Marten Feldtmann <marten(at)feki(dot)toppoint(dot)de>
To: Marten Feldtmann <marten(at)feki(dot)toppoint(dot)de>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] index usage ... strange !?
Date: 2000-01-19 17:46:26
Message-ID: 200001191746.SAA09643@feki.toppoint.de (view raw or flat)
Thread:
Lists: pgsql-sql
> 
>  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




In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2000-01-19 18:07:26
Subject: Re: [SQL] index usage ... strange !?
Previous:From: Tom LaneDate: 2000-01-19 17:24:33
Subject: Re: [SQL] char(19) to varchar(32)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group