Re: About "Our CLUSTER implementation is pessimal" patch - Mailing list pgsql-hackers

>one idea could be to actually prepare a query using SPI for "select * from table order by <cols>" and then peek inside
> to see which plan was generated.

I like that!!!
Here's a first attempt, it looks like it's working...
(I still have to skip non-btree indexes and expression indexes, plus
add a ASC/DESC to the select)

static bool use_index_scan(Relation OldHeap, Oid indexOid)
{
HeapTuple    indexTuple;
Form_pg_index indexForm;
struct _SPI_plan *spiPlan;
char st[(NAMEDATALEN+1)*INDEX_MAX_KEYS+NAMEDATALEN+100];
int i;
TupleDesc    oldTupDesc;
bool retval = true;
PlannedStmt *plan;
CachedPlanSource *cps;

oldTupDesc = RelationGetDescr(OldHeap);

sprintf(st, "select * from %s order by ", OldHeap->rd_rel->relname.data);
indexTuple = SearchSysCache(INDEXRELID, ObjectIdGetDatum(indexOid),0, 0, 0);

if (!HeapTupleIsValid(indexTuple))  elog(ERROR, "cache lookup failed for index %u", indexOid);

indexForm = (Form_pg_index) GETSTRUCT(indexTuple);

for (i = 0; i < indexForm->indnatts; i++)
{ strcat(st, oldTupDesc->attrs[indexForm->indkey.values[i]-1]->attname.data); if (i+1 < indexForm->indnatts) {
strcat(st,",");  } 
}

ReleaseSysCache(indexTuple);

if (SPI_connect() != SPI_OK_CONNECT)  return false;

spiPlan = SPI_prepare(st, 0, NULL);
if (spiPlan == NULL)
{ SPI_finish();  return false;
}

cps = (CachedPlanSource*)(list_head(spiPlan->plancache_list)->data.ptr_value);
plan = (PlannedStmt*)(list_head(cps->plan->stmt_list)->data.ptr_value);
if (IsA(plan->planTree, Sort))
{  retval = false;
}

SPI_freeplan(spiPlan);
SPI_finish();

return retval;
}