Force PostgreSQL to query a custom index structure

Started by Carsten Kropfabout 16 years ago2 messagesgeneral
Jump to latest
#1Carsten Kropf
ckropf2@fh-hof.de

Hi *,
I have a question according to a customly built index. I want to force the query planner to search in my index structure, whenever I have a query that uses my specified operator. Until now, I always have to insert a quite amount of tuples (~ 20000-30000) for being able to get an index scan instead of a sequential scan.
What I tried here is to set in my costestimate function:
*indexStartupCost = 1;
*indexTotalCost = 1;
*indexSelectivity = 1;
*indexCorrelation = 1;
However, this does not work, apparently. I still have to put a quite amount of tuples to the table and build my index on top of this. My costestimate function is called properly and returns these values. What do I have to adjust in order to force the query planner to use my index structure (which would be much easier for me to test) and not having to insert a real big amount of data items?

Best regards
Carsten Kropf

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Carsten Kropf (#1)
Re: Force PostgreSQL to query a custom index structure

Carsten Kropf <ckropf2@fh-hof.de> writes:

I have a question according to a customly built index. I want to force the query planner to search in my index structure, whenever I have a query that uses my specified operator. Until now, I always have to insert a quite amount of tuples (~ 20000-30000) for being able to get an index scan instead of a sequential scan.

This is a good thing. If you screw up the cost estimates to the point
where it won't do that, you will be very unhappy with the results.

regards, tom lane