Postgresql 13
En una query como esta:
explain select
*
from
"companies"
inner join "subclients" on "subclients"."id" = "companies"."subclient_id"
inner join "temas" on "temas"."id" = "companies"."main_tema_id"
left join "domains" on "domains"."id" = "companies"."domain_id"
left join "papers" on "papers"."id" = "domains"."paper_id"
left join "tematicas" on "tematicas"."id" = "domains"."tematica_id"
left join "special_values" on "special_values"."subclient_id" = "companies".
"subclient_id"
where
"companies"."id" in (1381059542, 1380939758, 1380939757, 1380939753)
and "companies"."subclient_id" in (13579, 13580)
and companies.fulldate >= '2025-01-31 09:30' and companies.fulldate <
'2025-02-04
09:30'
and "companies"."hidden_by_contact" is null
and (
(subclients.show_3300 is not true and companies.ep_file !~ '_print3300')
or subclients.show_3300 is true
)
and "domains"."p_or_d" in ('p', 'd', 'b')
Tengo este plan de ejecución:
Nested Loop Left Join (cost=6.70..54.38 rows=1 width=5276)
-> Nested Loop Left Join (cost=2.28..45.94 rows=1 width=2707)
-> Nested Loop Left Join (cost=2.13..41.74 rows=1 width=2688)
-> Nested Loop (cost=1.71..33.80 rows=1 width=2655)
-> Nested Loop (cost=1.28..25.36 rows=1 width=1781)
-> Nested Loop (cost=0.86..16.92 rows=1
width=1673)
-> Index Scan using
companies_fulldate_subclient_id_idx on companies (cost=0.57..8.60 rows=1
width=1113)
Index Cond: ((fulldate >= '2025-01-31
09:30:00'::timestamp without time zone) AND (fulldate < '2025-02-04
09:30:00'::timestamp without time zone))
Filter: ((hidden_by_contact IS NULL)
AND (subclient_id = ANY ('{13579,13580}'::integer[])) AND (id = ANY
('{1381059542,1380939758,1380939757,1380939753}'::integer[])))
-> Index Scan using subclients2_pkey on
subclients (cost=0.29..8.31 rows=1 width=560)
Index Cond: (id =
companies.subclient_id)
Filter: (((show_3300 IS NOT TRUE) AND
((companies.ep_file)::text !~ '_print3300'::text)) OR (show_3300 IS TRUE))
-> Index Scan using temas_pkey on temas
(cost=0.42..8.44 rows=1 width=108)
Index Cond: (id = companies.main_tema_id)
-> Index Scan using domains_pkey on domains
(cost=0.42..8.44 rows=1 width=874)
Index Cond: (id = companies.domain_id)
Filter: ((p_or_d)::text = ANY ('{p,d,b}'::text[]))
-> Index Scan using papers_pkey on papers (cost=0.42..7.93
rows=1 width=33)
Index Cond: (id = domains.paper_id)
-> Index Scan using tematicas_pkey on tematicas (cost=0.14..4.16
rows=1 width=19)
Index Cond: (id = domains.tematica_id)
-> Bitmap Heap Scan on special_values (cost=4.42..8.43 rows=1
width=2569)
Recheck Cond: (subclient_id = companies.subclient_id)
-> Bitmap Index Scan on special_values_subclient_id_idx
(cost=0.00..4.42 rows=1 width=0)
Index Cond: (subclient_id = companies.subclient_id)
ya hice un ANALYZE.
La tabla companies tiene millones de filas.
La clave primaria de companies es "id"
No entiendo porque postgres no utiliza primero que nada ese índice para
reducir las filas de resultado, existe forma de forzar a que lo use? o es
correcto que no lo use?
Desde ya muchas gracias por los comentarios.
Saludos.
Guillermo
