hi tom ...
i thought about creating an index on the expression but the problem
is that this is hardly feasable.
in 8.0 (what i have here) this would block the table and i would run
out of disk space as well. this is a 600 gb biest :(
what about the planner approach?
this would solve the problem for some other issues as well. an index
might not be flexible enough :(.
many thanks,
hans
On Sep 4, 2006, at 4:57 PM, Tom Lane wrote:
> Hans-Juergen Schoenig <postgres@cybertec.at> writes:
>> 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.
>
> Create an index on that expression.
>
> regression=# create table foo(x) as select x * '864 sec'::interval
> + now()::timestamp from generate_series(1,10000) x;
> SELECT
> regression=# analyze foo;
> ANALYZE
> regression=# explain select x::date from foo group by x::date;
> QUERY PLAN
> ---------------------------------------------------------------
> HashAggregate (cost=205.00..330.00 rows=10000 width=8)
> -> Seq Scan on foo (cost=0.00..180.00 rows=10000 width=8)
> (2 rows)
>
> regression=# create index fooi on foo((x::date));
> CREATE INDEX
> regression=# analyze foo;
> ANALYZE
> regression=# explain select x::date from foo group by x::date;
> QUERY PLAN
> ---------------------------------------------------------------
> HashAggregate (cost=205.00..206.26 rows=101 width=8)
> -> Seq Scan on foo (cost=0.00..180.00 rows=10000 width=8)
> (2 rows)
>
> regression=#
>
> I had to cheat a little bit here: I tried to do this example with a
> timestamptz column, and the index creation failed because
> timestamptz to
> date isn't immutable (it depends on TimeZone). If yours is too, you
> could perhaps do something involving AT TIME ZONE to generate an
> immutable conversion to date.
>
> It would perhaps make sense to provide a way to cue ANALYZE to compute
> stats on expressions that aren't actually being indexed, but I see no
> good reason to limit our attention to cast expressions.
>
> regards, tom lane
