Re: 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 full table scan.

Greetings, Klaus

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) ?
>
> At many cases i need only a small fragment of raws to be retrieved. But
> this extreme case is a real-scenario (not the most frequent but real).
>
> I try to find a way to achieve good performence even for the extreme
> case. Is there any way?
>
> ps. In bibliografy, there is a different alternative for indices. except
> th simple approach of <attr_val, rid> is the alternative <attr_val, set
> of rids>. The second means the attaches to each discrete attr_val the set
> o rid's of all raws with same attr_val. Is this alternative taken into
> account in postgres?
>
>
> On Mon, 7 Mar 2005, Jeff Davis wrote:
>
>
>>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 of the tuples. Also, make sure enable_seqscan is true
>>(in case you turned it off for testing or something and forgot).
>>
>>A seqscan is usually faster when a large proportion of the tuples are
>>returned because:
>>(1) It uses sequential I/O; whereas an index might access tuples in a
>>random order.
>>(2) It doesn't have to read the index's disk pages at all.
>>
>>I suspect you don't need to return all the tuples in the table. If you
>>include the details of a real scenario perhaps the people on the list
>>could be more helpful.
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>