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

Yet again on indices...

From: Jean-Paul ARGUDO <jean-paul(dot)argudo(at)idealx(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: sdinot(at)idealx(dot)com, dbarth(at)idealx(dot)com, jean-paul(dot)argudo(at)idealx(dot)com
Subject: Yet again on indices...
Date: 2002-02-27 09:48:15
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

I'm working on query analysis for a program in ecpg for business puposes. Look
at what I found on with PG 7.2: Please be cool with my french2english processor,
I got few bogomips in my brain dedicated to english (should have listen more in

line 962 (in the ecpg source..)

EXPLAIN SELECT t12_bskid, t12_pnb, t12_lne, t12_tck
FROM T12_20011231
WHERE t12_bskid >= 1   
ORDER BY t12_bskid, t12_pnb, t12_tck, t12_lne;

Sort  (cost=3006.13..3006.13 rows=25693 width=46)
  ->  Seq Scan on t12_20011231  (cost=0.00..1124.20 rows=25693 width=46)

=> not good, table t12_20011231 as 26K tuples :-(

=> create index t12_idx_bskid_20011231 on t12_20011231 (t12_bskid);

Sort  (cost=3006.13..3006.13 rows=25693 width=46)
  ->  Seq Scan on t12_20011231  (cost=0.00..1124.20 rows=25693 width=46)

=> probably statistic refresh to be done: 
$ /usr/local/pgsql/bin/vacuumdb --analyze dbks

Sort  (cost=3006.13..3006.13 rows=25693 width=46)
  ->  Seq Scan on t12_20011231  (cost=0.00..1124.20 rows=25693 width=46)

=> Uh? Seq scan cheaper than index???  

=> let's disable seqscan to read cost of index:
postgresql.conf : enable_seqscan = false

Sort  (cost=3126.79..3126.79 rows=25693 width=46)
  ->  Index Scan using t12_idx_bskid_20011231 on t12_20011231
(cost=0.00..1244.86 rows=25693 width=46)

=> Uh? seq scan'cost is lower than index scan??  => mailto hackers


What's your opinion? 

I have to tell that this select opperates in a forloop statment . 
I hardly believe reading 26K tuples is cheaper thant index reading, but maybe
you'll ask me about buffers that should store de 26K tuples?...

But just after this query, there is another one that maybe will put data in
buffers, kicking t12_20011231 data blocks...

Well I feel a little stuck there. I'll continue with enable_scans=false, but
I feel bad beeing forced to do so... and still asking myself if this is good

Thanks for support, best regards.

Jean-Paul ARGUDO


pgsql-hackers by date

Next:From: Rod TaylorDate: 2002-02-27 11:50:38
Subject: Re: Refactoring of command.c
Previous:From: Zeugswetter Andreas SB SDDate: 2002-02-27 09:19:40
Subject: Re: COPY incorrectly uses null instead of an empty string in last field

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