i am looking at some corner case which might also cause troubles for other people.
consider the following:

SELECT some_timestamp::date FROM very_large_table GROUP BY some_timestamp::date

my very_large_table is around 1billion entries.
the problem is: the planner has a problem here as it is taking the (correct) estimates for timestamp. this avoids a HashAggregate because the dataset seems to large for work_mem. what the planner cannot know is that the number of days is quite limited (in my case around 1000 different values).
i wonder how to teach the planner to take the cast into consideration.

at the moment the planner uses the per column statistics - it cannot know that the cast might change the number of different values.
how about the following?

Command:     CREATE CAST
Description: define a new cast
CREATE CAST (sourcetype AS targettype)
    [USING SELECTIVITY number | funcname(argtypes)]
    WITH FUNCTION funcname (argtypes)

if it was possible to assign a constant or some function to the cast i think we could make the example used above work. by default no costs are changed. if somebody is doing some fancy query it would be possible to tweak GOUOP BY planning by assigning some cleverly written function or a constant to the scenery.

a constant would be useful in terms of casts to boolean or so.

does anybody have an idea which could help solving this issue?

        best regards,


---------------------------(end of broadcast)---------------------------
TIP 1: 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

Reply via email to