Thread: Postgres index performance

Postgres index performance

From
Simon Windsor
Date:
Hi

Following up the performance problems I am having in migrating an
application to Postgres I have noticed that one query gives me

ncndb=# explain select code from tableA where code=9;
                           QUERY PLAN
----------------------------------------------------------------
 Seq Scan on tableA  (cost=0.00..18451.99 rows=2 width=8)
   Filter: (code = 9)
(2 rows)

whilst another provides

ncndb=# explain select code from tableB where code=123;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using contacts_pk on contacts  (cost=0.00..3.71 rows=1
width=4)
   Index Cond: (code = 123)
(2 rows)

The two table are a similar size, and have a field, code, as their
primary key. In tableA, code is a bigint, whilst in tableB is an
integer.

Is there a reason why the table with the bigint code column does full
table scans (cost 18500), whilst the other uses an index scan (cost
3.7).

Many thanks

Simon


--
Simon Windsor
Email: simon.windsor@cornfield.org.uk
Tel: 01454 617689
Mob: 07960 321599


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.