Jaja si engaño a pg y cambio la condición and companies.fulldate::text >= '2025-01-31 09:30' and companies.fulldate:: text < '2025-02-04 09:30'
ya no usa el índice secundario, usa el índice de la PK y resuelve la query en menos de un segundo , lo tengo resuelto así pero me quedo con la duda de porque el planificador lo hace mal de la otra manera (para mi) El mié, 5 feb 2025 a las 9:02, Guillermo E. Villanueva (< guillermo...@gmail.com>) escribió: > 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
