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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Chuck Bernardes | 2004-06-30 21:17:15 | PostgreSQL 7.4.3 on a windows XP Pro environment Installation |
Previous Message | Richard Welty | 2004-06-30 20:45:52 | Re: backups |