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
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) |