Re: The Axe list

tomas(at)tuxteam(dot)de wrote:
> So it seems that intagg should rather live in a section "examples" than
> in contrib?

Perhaps. Seems my old intagg use case from 8.1 is not really needed
anymore since it seems ANY got much smarter since then. Cool.

=================================================================================
== With 8.1
=================================================================================
fli=# explain analyze
fli-# select * from lines, selected_lines
fli-# where selected_lines.id = 16238 and tlid = ANY (line_ids);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=24.51..16899784.67 rows=166031616 width=202) (actual time=0.980..615547.605 rows=2 loops=1)
Join Filter: ("outer".tlid = ANY ("inner".line_ids))
-> Seq Scan on lines (cost=0.00..1956914.72 rows=55343872 width=166) (actual time=0.012..160106.897 rows=55291697 loops=1)
-> Materialize (cost=24.51..24.57 rows=6 width=36) (actual time=0.002..0.003 rows=1 loops=55291697)
-> Seq Scan on selected_lines (cost=0.00..24.50 rows=6 width=36) (actual time=0.012..0.016 rows=1 loops=1)
Filter: (id = 16238)
Total runtime: 615547.680 ms
(7 rows)

fli=# explain analyze
fli-# select *
fli-# from lines,
fli-# (select int_array_enum(line_ids) as lid from selected_lines where id=16238) as z
fli-# where lid = lines.tlid;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..54.42 rows=6 width=170) (actual time=36.755..42.268 rows=2 loops=1)
-> Seq Scan on selected_lines (cost=0.00..24.52 rows=6 width=32) (actual time=0.023..0.029 rows=2 loops=1)
Filter: (id = 16238)
-> Index Scan using rtgr_lines__tlid on lines (cost=0.00..4.96 rows=1 width=166) (actual time=21.105..21.108 rows=1 loops=2)
Index Cond: ("outer"."?column1?" = lines.tlid)
Total runtime: 42.352 ms
(6 rows)

=================================================================================
== With HEAD
=================================================================================

fli=# explain analyze
select * from lines, selected_lines
where selected_lines.id = 16238 and tlid = ANY (line_ids);

fli-# fli-# QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=36.44..424.78 rows=61 width=210) (actual time=0.335..0.421 rows=2 loops=1)
-> Seq Scan on selected_lines (cost=0.00..24.50 rows=6 width=36) (actual time=0.018..0.021 rows=1 loops=1)
Filter: (id = 16238)
-> Bitmap Heap Scan on lines (cost=36.44..66.49 rows=10 width=174) (actual time=0.293..0.374 rows=2 loops=1)
Recheck Cond: (lines.tlid = ANY (selected_lines.line_ids))
-> Bitmap Index Scan on rtgr_lines__tlid (cost=0.00..36.44 rows=10 width=0) (actual time=0.251..0.251 rows=2 loops=1)
Index Cond: (lines.tlid = ANY (selected_lines.line_ids))
Total runtime: 0.653 ms
(8 rows)