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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
