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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

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