Thread: Query Plan

<br /><p><font face="Arial" size="2">Have a question regarding when a Query Plan uses an Index.</font><p><font
face="Arial"size="2">I have a basic statement</font><p><font face="Arial" size="2">This query uses the Index
Scan:</font><br/><font face="Arial" size="2">explain analyse select * from tablea where columna_id <
57</font><p><fontface="Arial" size="2">This query uses Seq Scan:</font><br /><font face="Arial" size="2">explain
analyseselect * from tablea where columna_id < 58</font><p><font face="Arial" size="2">There are about 5000 records
inthis table, and columna_id is the primary key, which is Indexed... And recently recreated.</font><p><font
face="Arial"size="2">My question is why is the Plan not using the index on the second occasions?</font><p><font
face="Arial"size="2">Theo</font><table><tr><td bgcolor="#ffffff"><font
color="#000000">______________________________________________________________________<br/>This email, including
attachments,is intended only for the addressee<br />and may be confidential, privileged and subject to copyright. If
you<br/>have received this email in error, please advise the sender and delete<br />it. If you are not the intended
recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br
/>communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table> 

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