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

<p>one idea could be to actually prepare a query using SPI for "select * from table order by <cols>" and then
peekinside to see which plan was generated. perhaps you could do this using the existing planner hook. <p>you might
haveto watch out for the user's rules or planner hooks (though I don't think referential integrity triggers take any
precautionsabout those dangers)<p>greg<p><blockquote type="cite">On 20 Jan 2010 17:48, "Leonardo F" <<a
href="mailto:m_lists@yahoo.it">m_lists@yahoo.it</a>>wrote:<br /><br /><p><font color="#500050">> I read the
thread"Our CLUSTER implementation is pessimal" > <a
href="http://archives.postgresql.org/pgsql.">http://archives.postgresql.org/pgsql.</a>..</font>Ithink I got something
upand running to check if a table scan + sort is supposed<br /> to be faster than an index scan for a certain CLUSTER
operation.<br/><br /> The way I did it is (I guess...) wrong: I created the elements needed by<br /> get_relation_info,
create_seqscan_path,create_index_path, cost_sort.<br /><br /> It has been, obviously, a trial and error approach: I
addedthe member values as<br /> soon as one function call crashed... and I bet I didn't get all the corner cases.<br />
Isthere any better way of doing it?<br /><br /> Leonardo<br /><br /> (this is called in copy_heap_data to decide which
pathto choose:)<br /><br /> static bool use_index_scan(Oid tableOid, Oid indexOid)<br /> {<br /> RelOptInfo *rel;<br />
PlannerInfo*root;<br /> Query *query;<br /> PlannerGlobal *glob;<br /> Path *seqAndSortPath;<br /> IndexPath
*indexPath;<br/> RangeTblEntry *rte;<br /><br /> rel = makeNode(RelOptInfo);<br /> rel->reloptkind =
RELOPT_BASEREL;<br/> rel->relid = 1;<br /> rel->rtekind = RTE_RELATION;<br /><br /> /* needed by
get_relation_info*/<br /> glob = makeNode(PlannerGlobal);<br /><br /> /* needed by get_relation_info: */<br /> query =
makeNode(Query);<br/> query->resultRelation = 0;<br /><br /> root = makeNode(PlannerInfo);<br /><br />
root->parse= query;<br /> root->glob = glob;<br /><br /> get_relation_info(root, tableOid, false, rel);<br />
seqAndSortPath= create_seqscan_path(NULL, rel);<br /><br /> rel->rows = rel->tuples;<br /><br /> rte =
makeNode(RangeTblEntry);<br/> rte->rtekind = RTE_RELATION;<br /> rte->relid = tableOid;<br /><br />
root->simple_rel_array_size= 2;<br /> root->simple_rte_array = (RangeTblEntry **)<br />
palloc0(root->simple_rel_array_size* sizeof(RangeTblEntry *));<br /> root->simple_rte_array[1] = rte;<br /><br />
root->total_table_pages= rel->pages;<br /><br /> indexPath = create_index_path(root,
(IndexOptInfo*)(list_head(rel->indexlist)->data.ptr_value),NULL, NULL, ForwardScanDirection, NULL);<br />
cost_sort(seqAndSortPath,root, NULL, seqAndSortPath->total_cost, rel->tuples, rel->width, -1);<br /><br />
returnindexPath->path.total_cost < seqAndSortPath->total_cost;<br /><p><font color="#500050">} -- Sent via
pgsql-hackersmailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>) To make
changesto you...</font></blockquote>