Boa Tarde Tem como otimizar a querie abaixo para deixa-la mais rapida e com menos union
SELECT * FROM (SELECT A.* FROM "test_table4" A WHERE ( A."code_id" >= E'00010' AND A."days" >= 990 AND A."date_lim" >= '20150128' AND A."sr_recno" >= 10 ) ORDER BY A."code_id" NULLS FIRST, A."days" NULLS FIRST, A."date_lim" NULLS FIRST, A."sr_recno" NULLS FIRST LIMIT 102 ) TMP1 UNION SELECT * FROM (SELECT A.* FROM "test_table4" A WHERE ( A."code_id" >= E'00010' AND A."days" = 990 AND A."date_lim" > '20150128' ) ORDER BY A."code_id" NULLS FIRST, A."days" NULLS FIRST, A."date_lim" NULLS FIRST, A."sr_recno" NULLS FIRST LIMIT 102 ) TMP2 UNION SELECT * FROM (SELECT A.* FROM "test_table4" A WHERE ( A."code_id" = E'00010' AND A."days" > 990 ) ORDER BY A."code_id" NULLS FIRST, A."days" NULLS FIRST, A."date_lim" NULLS FIRST, A."sr_recno" NULLS FIRST LIMIT 102 ) TMP3 UNION SELECT * FROM (SELECT A.* FROM "test_table4" A WHERE ( A."code_id" > E'00010' ) ORDER BY A."code_id" NULLS FIRST, A."days" NULLS FIRST, A."date_lim" NULLS FIRST, A."sr_recno" NULLS FIRST LIMIT 102 ) TMP4 ORDER BY "code_id" NULLS FIRST, "days" NULLS FIRST, "date_lim" NULLS FIRST, "sr_recno" NULLS FIRST LIMIT 102 /* Skip FWD */ o .sql que cria a tabela, indices e popula os dados esta em https://www.dropbox.com/s/1qarltxmwsp63fp/data.sql?dl=0 o que preciso fazer e paginar os dados sempre mantendo a ordem ou seja pegar os proximos 102 registros;. o explain analize dela nao e ruim e tava pensando se e possivel fazer a mesma coisa sem union abaixo o resultado do explain "Limit (cost=33.36..33.37 rows=4 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " -> Sort (cost=33.36..33.37 rows=4 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " Sort Key: a.code_id, a.days, a.date_lim, a.sr_recno" " -> HashAggregate (cost=33.28..33.32 rows=4 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " -> Append (cost=0.00..33.17 rows=4 width=206)" " -> Limit (cost=0.00..8.28 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " -> Index Scan using test_table4_ind03_000003 on public.test_table4 a (cost=0.00..8.28 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " Index Cond: (((a.code_id)::text >= '00010'::text) AND (a.days >= 990::numeric) AND (a.date_lim >= '2015-01-28'::date) AND (a.sr_recno >= 10::numeric))" " -> Limit (cost=0.00..8.27 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " -> Index Scan using test_table4_ind03_000003 on public.test_table4 a (cost=0.00..8.27 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " Index Cond: (((a.code_id)::text >= '00010'::text) AND (a.days = 990::numeric) AND (a.date_lim > '2015-01-28'::date))" " -> Limit (cost=0.00..8.27 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " -> Index Scan using test_table4_ind03_000003 on public.test_table4 a (cost=0.00..8.27 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " Index Cond: (((a.code_id)::text = '00010'::text) AND (a.days > 990::numeric))" " -> Limit (cost=0.00..8.27 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " -> Index Scan using test_table4_ind03_000003 on public.test_table4 a (cost=0.00..8.27 rows=1 width=206)" " Output: a.code_id, a.cardid, a.descr, a.percent, a.days, a.date_lim, a.enable, a.obs, a.value, a.sr_recno, a.sr_deleted" " Index Cond: ((a.code_id)::text > '00010'::text)" banco postgresql 9.3.5 so windows e linux Atenciosamente Luiz Rafael Culik Guimaraes
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
