Skip site navigation (1) Skip section navigation (2)

Postgres index performance

From: Simon Windsor <simon(dot)windsor(at)cornfield(dot)org(dot)uk>
To: Postgres List <pgsql-general(at)postgresql(dot)org>
Subject: Postgres index performance
Date: 2004-06-30 21:02:03
Message-ID: (view raw or whole thread)
Lists: pgsql-general

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

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

Many thanks


Simon Windsor
Email: simon(dot)windsor(at)cornfield(dot)org(dot)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.

pgsql-general by date

Next:From: Chuck BernardesDate: 2004-06-30 21:17:15
Subject: PostgreSQL 7.4.3 on a windows XP Pro environment Installation
Previous:From: Richard WeltyDate: 2004-06-30 20:45:52
Subject: Re: backups

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group