Re: [HACKERS] About b-tree usage

On Tue, 2005-03-08 at 15:30 +0200, Ioannis Theoharis wrote: > > let me, i have turned enable_seqscan to off, in order to discourage > optimizer to choose seq_scan whenever an idex_scan can be used. > > But in this case, why optimizer don't chooses seq_scan (discourage is > different than prevent)

Re: [HACKERS] About b-tree usage

Hi, if you're using a pg version prio to 8.0 your pitfall might also be a conversion between int and bigint datatypes. So if you're doing somthing like SELECT a.x, b.y, c.y FROM a, b WHERE a.x = b.x; and a.x is INT4 and b.x is INT8 (or BIGINT) the planner counts this as a data conversion and uses a

Re: [HACKERS] About b-tree usage

Ioannis Theoharis wrote: let me, i have turned enable_seqscan to off, in order to discourage optimizer to choose seq_scan whenever an idex_scan can be used. But in this case, why optimizer don't chooses seq_scan (discourage is different than prevent) ? You probably know that PostgreSQL uses a cost-

Re: [HACKERS] About b-tree usage

let me, i have turned enable_seqscan to off, in order to discourage optimizer to choose seq_scan whenever an idex_scan can be used. But in this case, why optimizer don't chooses seq_scan (discourage is different than prevent) ? At many cases i need only a small fragment of raws to be retrieved.

Re: [HACKERS] About b-tree usage

Ioannis Theoharis <[EMAIL PROTECTED]> writes: > select att0 from tc20 where att1=9 AND att1>=0 > plan: > Index Scan using inst_id_idx on tc20 (cost=0.00..161603.06 > rows=106 width=1004) (actual time=41.21..101917.36 rows=100 loops=1) >Index Cond: ((att1 <= 90

Re: [HACKERS] About b-tree usage

In that case, sequential scan is faster, but perhaps the planner doesn't know that ahead of time. Try turning on more statistics if you haven't already, and then run ANALYZE again. If the planner sees a range, perhaps it assumes that it is a highly selective range, when in fact, it consists of all

Re: [HACKERS] About b-tree usage

> If there are many identical values in att0, are you sure a sequential > scan isn't more efficient? Also, are you sure the index isn't working > well? It seems to me since you have the table clustered, it might be > fairly efficient as-is (it would get a huge benefit from the spatial > locality o

Re: [HACKERS] About b-tree usage

If I understand your question, you want to reduce the index size by only pointing to the first tuple in a table with a given key in att0, since the rest of the tuples will be right afterward (because you keep the table clustered on that key). However, from the docs: