Re: The Axe list - Mailing list pgsql-hackers

tomas@tuxteam.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
 

--------------------------------------------------------------------------------------------------------------------------------
NestedLoop  (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)
(actualtime=0.012..160106.897 rows=55291697 loops=1)   ->  Materialize  (cost=24.51..24.57 rows=6 width=36) (actual
time=0.002..0.003rows=1 loops=55291697)         ->  Seq Scan on selected_lines  (cost=0.00..24.50 rows=6 width=36)
(actualtime=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

----------------------------------------------------------------------------------------------------------------------------------
NestedLoop  (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=1loops=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

---------------------------------------------------------------------------------------------------------------------------------
NestedLoop  (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.44rows=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)