Es de tipo integer
probé con "companies"."id" = ANY (ARRAY[1381059542::integer, 1380939758::
integer, 1380939757::integer, 1380939753::integer])
pero lo sigue poniendo como filtro dentro de la búsqueda de otro indice que
no es PK
El mar, 4 feb 2025 a las 19:49, Jairo Graterón (<jgrate...@gmail.com>)
escribió:
> 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 (<
> guillermo...@gmail.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
>>
>
