BEGIN;
	CREATE schema test;
END;

BEGIN;
	CREATE TABLE test.test_group
	(
	  id_group			Integer
	, id_ticket		Integer
	, "date"		date
	, description	text
	,primary key(id_group)
	);

	create table test.tickets
	(id_ticket		Integer
	, ticket_name	text
	,primary key(id_ticket)
	);
END;
BEGIN;
	WITH tck (id_ticket , ticket_name) AS 
	(VALUES 
		(1,'01 t')
		,(2,'02 t')
		,(3,'03 t')
		,(4,'04 t')
		,(5,'05 t')
		,(6,'06 t')
		,(7,'07 t')
		,(8,'08 t')
		,(9,'09 t')
		,(10,'10 t')
	)
	INSERT INTO test.tickets (id_ticket , ticket_name)
	SELECT * FROM tck WHERE NOT exists (select 1 from test.tickets t WHERE t.id_ticket=tck.id_ticket );
END;
TRUNCATE test.test_group;
BEGIN;
	WITH g (f) AS  (SELECT generate_series(1,100000) g)
	INSERT INTO test.test_group
	(
	  id_group	--	Integer
	, id_ticket	--	Integer
	, "date"	--	date
	, description	--text
	)
	SELECT
		f
		, (f%10)+1 
		, '2014-01-01'::date + (f%11)+ (f%13) +(f%17) + (f%19)+ (f%23)+ (f%27)
		, 'g '|| f::text
	FROM g;
END;
BEGIN;
	CREATE INDEX 
	ON test.test_group (id_ticket,"date");
END;

ANALYZE test.test_group;
ANALYZE test.tickets;

SELECT
	id_group
	,id_ticket
	,description
	,date
FROM (
	SELECT
		id_group
		,id_ticket,
		max("date") over(partition by id_ticket) as max_date,
		date,
		description
	FROM test.test_group
    ) as t1
WHERE max_date = date;
----explayn analyze-----------
"Subquery Scan on t1  (cost=0.29..8176.72 rows=500 width=19) (actual time=111.026..1051.965 rows=12 loops=1)"
"  Filter: (t1.max_date = t1.date)"
"  Rows Removed by Filter: 99988"
"  ->  WindowAgg  (cost=0.29..6926.72 rows=100000 width=19) (actual time=62.715..819.192 rows=100000 loops=1)"
"        ->  Index Scan using test_group_id_ticket_date_idx on test_group  (cost=0.29..5426.72 rows=100000 width=19) (actual time=0.019..304.252 rows=100000 loops=1)"
"Total runtime: 1052.187 ms"

SELECT
	id_group
	,t.id_ticket
	,g.description
	,date
	
FROM test.tickets t
,LATERAL (SELECT * FROM test.test_group g WHERE g.id_ticket = t.id_ticket ORDER BY date DESC limit 1) g;
------explain analyze---------------
"Nested Loop  (cost=0.29..7.08 rows=10 width=19) (actual time=0.043..0.382 rows=10 loops=1)"
"  ->  Seq Scan on tickets t  (cost=0.00..1.10 rows=10 width=4) (actual time=0.006..0.027 rows=10 loops=1)"
"  ->  Limit  (cost=0.29..0.58 rows=1 width=19) (actual time=0.020..0.022 rows=1 loops=10)"
"        ->  Index Scan Backward using test_group_id_ticket_date_idx on test_group g  (cost=0.29..2851.48 rows=10000 width=19) (actual time=0.011..0.011 rows=1 loops=10)"
"              Index Cond: (id_ticket = t.id_ticket)"
"Total runtime: 0.445 ms"

WITH foo (id_ticket,date )
AS (SELECT t.id_ticket,date
		FROM test.tickets t
		,LATERAL (SELECT * FROM test.test_group g WHERE g.id_ticket = t.id_ticket ORDER BY date DESC limit 1) foo
	)

SELECT
	g.id_group
	,g.id_ticket
	,g.description
	,g.date
FROM test.test_group g
WHERE  (id_ticket,date ) IN (SELECT *
		FROM foo
	)
ORDER BY 2,3;
------explain ....------------------------
"Sort  (cost=2683.69..2711.47 rows=11111 width=19) (actual time=1.823..1.876 rows=12 loops=1)"
"  Sort Key: g.id_ticket, g.description"
"  Sort Method: quicksort  Memory: 17kB"
"  CTE foo"
"    ->  Nested Loop  (cost=0.29..7.08 rows=10 width=8) (actual time=0.079..0.733 rows=10 loops=1)"
"          ->  Seq Scan on tickets t  (cost=0.00..1.10 rows=10 width=4) (actual time=0.013..0.058 rows=10 loops=1)"
"          ->  Limit  (cost=0.29..0.58 rows=1 width=19) (actual time=0.035..0.040 rows=1 loops=10)"
"                ->  Index Scan Backward using test_group_id_ticket_date_idx on test_group g_1  (cost=0.29..2851.48 rows=10000 width=19) (actual time=0.017..0.017 rows=1 loops=10)"
"                      Index Cond: (id_ticket = t.id_ticket)"
"  ->  Nested Loop  (cost=5.68..1929.97 rows=11111 width=19) (actual time=0.976..1.718 rows=12 loops=1)"
"        ->  HashAggregate  (cost=0.25..0.35 rows=10 width=8) (actual time=0.908..0.955 rows=10 loops=1)"
"              ->  CTE Scan on foo  (cost=0.00..0.20 rows=10 width=8) (actual time=0.095..0.841 rows=10 loops=1)"
"        ->  Bitmap Heap Scan on test_group g  (cost=5.43..191.85 rows=111 width=19) (actual time=0.030..0.036 rows=1 loops=10)"
"              Recheck Cond: ((id_ticket = foo.id_ticket) AND (date = foo.date))"
"              ->  Bitmap Index Scan on test_group_id_ticket_date_idx  (cost=0.00..5.40 rows=111 width=0) (actual time=0.010..0.010 rows=1 loops=10)"
"                    Index Cond: ((id_ticket = foo.id_ticket) AND (date = foo.date))"
"Total runtime: 2.073 ms"