https://explain.depesz.com/s/R3Fh
Si lo ejecuto solo hasta el filtro de companies.id devuelve el resultado en menos de 1 segundo Ese campo es PK de la tabla companies, si lo resuelve tan rápido al de las PK porque priorizar un indice de la misma tabla donde las columnas no son PK? El mar, 4 feb 2025 a las 22:34, Horacio Miranda (<hmira...@gmail.com>) escribió: > Usa depez para compartir el plan de ejecución por favor > New explain | explain.depesz.com <https://explain.depesz.com/> > explain.depesz.com > [image: favicon.ico] <https://explain.depesz.com/> > <https://explain.depesz.com/> > > > Usa esta forma > explain (analyze,buffers) select .. > > > Regards, > Horacio Miranda > > On 5 Feb 2025, at 11:49 AM, Jairo Graterón <jgrate...@gmail.com> wrote: > > > 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 >> >
favicon.ico
Description: Binary data
