Re: indexes and big tables

On Fri, 27 Jul 2001, Robert Vojta wrote:
> netacc=> EXPLAIN (SELECT SUM(counterfrom) AS from, SUM(counterto) AS to,
> floor((985098900 - date_part('epoch', counterstamp)) / 300) AS sequence
> FROM counters WHERE line='absolonll' AND date_part('epoch', counterstamp)
> > 984978900 GROUP BY sequence, line) UNION (SELECT SUM(counterfrom) AS
> from, SUM(counterto) AS to, floor((985098900 - date_part('epoch',
> counterstamp)) / 300) AS sequence FROM static_counters WHERE
> line='absolonll' AND date_part('epoch', counterstamp) > 984978900 GROUP BY
> sequence, line); NOTICE: QUERY PLAN:

Is there any possibility of overlapping rows between the parts of the
union? If not, I'd suggest union all, since that might get rid of the top
level unique and sort steps (probably not a huge gain, but might help).

> Unique (cost=67518.73..67525.44 rows=89 width=36)
> -> Sort (cost=67518.73..67518.73 rows=895 width=36)
> -> Append (cost=1860.01..67474.87 rows=895 width=36)
> -> Aggregate (cost=1860.01..1870.90 rows=109 width=36)
> -> Group (cost=1860.01..1865.46 rows=1089 width=36)
> -> Sort (cost=1860.01..1860.01 rows=1089
> width=36)
> -> Seq Scan on counters
> (cost=0.00..1805.10 rows=1089 width=36)
> -> Aggregate (cost=65525.38..65603.97 rows=786 width=36)
> -> Group (cost=65525.38..65564.67 rows=7858
> width=36)
> -> Sort (cost=65525.38..65525.38 rows=7858
> width=36)
> -> Seq Scan on static_counters
> (cost=0.00..65016.95 rows=7858 width=36)
>
> EXPLAIN
> netacc=>