Query Plan

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C4962E.29F3C4C0
Content-Type: text/plain



Have a question regarding when a Query Plan uses an Index.

I have a basic statement

This query uses the Index Scan:
explain analyse select * from tablea where columna_id < 57

This query uses Seq Scan:
explain analyse select * from tablea where columna_id < 58

There are about 5000 records in this table, and columna_id is the primary
key, which is Indexed... And recently recreated.

My question is why is the Plan not using the index on the second occasions?

Theo


____________________________________________________________ __________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
------_=_NextPart_001_01C4962E.29F3C4C0
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

Have a question regarding when a Query Pla=
n uses an Index.

I have a basic statement

This query uses the Index Scan:

explain analyse select * from tablea wher=
e columna_id < 57

This query uses Seq Scan:

explain analyse select * from tablea wher=
e columna_id < 58

There are about 5000 records in this table=
, and columna_id is the primary key, which is Indexed... And recently recre=
ated.

My question is why is the Plan not using t=
he index on the second occasions?

Theo

Re: Query Plan

Theo Galanakis wrote:
>
> Have a question regarding when a Query Plan uses an Index.
>
> I have a basic statement
>
> This query uses the Index Scan:
> explain analyse select * from tablea where columna_id < 57
>
> This query uses Seq Scan:
> explain analyse select * from tablea where columna_id < 58
>
> There are about 5000 records in this table, and columna_id is the primary
> key, which is Indexed... And recently recreated.

OK - what does explain analyse say?

Each line will have two parts, the expected costs/number of rows and the
actual time/number of rows.

If PG is expecting say 50 rows in the first query but 150 in the second
then it might decide a sequential scan is quicker.

Post the outputs of explain analyse and we can compare them.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org