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: 1088629323.7811.76.camel@celt.cornfield.org.uk (view raw or flat)
Thread:
Lists: pgsql-general
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(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-2014 The PostgreSQL Global Development Group