Re: The Axe list - Mailing list pgsql-hackers

From Ron Mayer
Subject Re: The Axe list
Date
Msg-id 48F1630E.6030105@cheapcomplexdevices.com
Whole thread Raw
In response to Re: The Axe list  (Josh Berkus <josh@agliodbs.com>)
List pgsql-hackers
Josh Berkus wrote:
> So it sounds like intagg is still in use/development.  But ... is it 
> more of an example, or is it useful as a type/function in production?

Where I work we (and our customers) use it in our production systems.

At first glance it seems our reasons for using it are mostly
legacy reasons dating to 8.1 where intagg was the best way to
write some queries.  At least some of these seem to be unnecessary
with 8.3.  If intagg's at risk of going away soon I could
further check the range of queries where we use it against 8.3
or CVS head if that's useful to the discussion.
From our testing notes, here's another 8.1 query where we had
order-of-magnitude speedups using intagg and friends.
-- with 30000
-- explain analyze select fac_nam  from userfeatures.point_features  join entity_facets using (entity_id)  where
featureid=115group by fac_nam;
 
-- -- Total runtime: 7125.322 ms
-- select fac_nam from (select distinct int_array_enum(fac_ids) as fac_id from (select distinct fac_ids from
entity_facidsnatural join point_features where featureid=115) as a) as a join facet_lookup using (fac_id);
 
-- -- Total runtime: 1297.558 ms
-- explain analyze select fac_nam from (select int_array_enum(fac_ids) as fac_id from (select fac_ids from
entity_facidsnatural join point_features where featureid=115 group by fac_ids) as a group by int_array_enum(fac_ids))
asa join facet_lookup using (fac_id) order by fac_nam;
 
-- -- Total runtime: 1164.258 ms
-- explain analyze select fac_nam from (select distinct int_array_enum(fac_ids) as fac_id from (select
intarray_union_agg(fac_ids)as fac_ids from entity_facids natural join point_features where featureid=115) as a) as a
joinfacet_lookup using (fac_id);
 
-- -- Total runtime: 803.187 ms
I can check it on 8.3 monday.


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: recursive query crash
Next
From: Tom Lane
Date:
Subject: Re: The Axe list