Re: plan de ejecución

Saludos

id es de tipo int o bigint?

Prueba con esta otra forma de hacer el IN y nos comentas.

"companies"."id" = ANY (ARRAY[1381059542, 1380939758, 1380939757,
1380939753]::integer)

si es tipo bigint

"companies"."id" = ANY (ARRAY[1381059542, 1380939758, 1380939757,
1380939753]::bigint[])

El mar, 4 feb 2025 a las 12:43, Guillermo E. Villanueva (<
guillermovil(at)gmail(dot)com>) escribió:

> 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
>