optimizing a query

[Jonathan Vanasco <postgres@xxxxxxxx>]

I have a handful of queries in the following general form that I can't seem to optimize any further (same results on 9.3, 9.4, 9.5)

I'm wondering if anyone might have a suggestion, or if they're done.

The relevant table structure:

	t_a2b
		a_id INT references t_a(id)
		b_id INT references t_b(id)
		col_a

	t_a
		id INT
		col_1 INT
		col_2 BOOL

The selects query the association table (t_a2b) and join in a related table (t_a) for some filtering.

In effort of simplifying the work, I've created indexes on t_a that have all the related columns.

	CREATE INDEX test_idx ON t_a(col_1, id) WHERE col_2 IS NOT FALSE;
        CREATE INDEX test_idx__a ON t_a(col_1, id) WHERE col_2 IS NOT FALSE;

postgres will query test_idx__a first (yay!) but then does a bitmap heap scan on t_a, and uses the raw t_a for the hash join.  

I don't actually need any information from t_a - it's just there for the filtering, and ideally postgres would just use the index.

I thought this might have been from using a partial index, but the same results happen with a full index.  I just can't seem to avoid this hash join against the full table.

anyone have a suggestion?


example query

	SELECT t_a2b.b_id AS b_id,
		   count(t_a2b.b_id) AS counted
	FROM t_a2b 
	WHERE 
		  t_a2b.col_a = 1
		  AND
		  t_a.col_1 = 730
		  AND
		  t_a.col_2 IS NOT False
	GROUP BY t_a2b.b_id
	ORDER BY 	counted DESC,
			 	t_a2b.b_id ASC                                                                                                                                                                                                                                                     



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general