Re: Particionamiento de tablas

El 29/11/05, Jaime Casanova<systemguards(at)gmail(dot)com> escribió:
> On 11/28/05, Ing. Jhon Carrillo // Caracas, Venezuela
> <jhon(dot)carrillo(dot)foros(at)gmail(dot)com> wrote:
> > Saludos,
> >
> > estoy haciendo pruebas con el particionamiento de tablas del 8.1, pero
> > cuando hago un explain de un query ej. select * from
> > tabla_particionada where campo=valor el motor chequea todos los
> > indices de todas las tablas secundarias, ¿Esto es correcto? ¿Debería
> > el motor chequear solo los indices donde esta la información?
> >
> > ya coloque SET constraint_exclusion = on;
> >
>
> podemos ver el explain analyze?

Tengo 50.000.000 de filas en la tabla para probar el rendimiento,
notese, que en la tabla default se encuentran la mayoría de los
registros.

Aggregate (cost=483972.68..483972.69 rows=1 width=0)
-> Append (cost=0.00..483436.87 rows=214322 width=0)
-> Seq Scan on audience_data (cost=0.00..16.75 rows=3 width=0)
Filter: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0501 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0501_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0502 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0502_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0503 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0503_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0504 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0504_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0505 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0505_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0506 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0506_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0601 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0601_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0602 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0602_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0603 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0603_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0604 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0604_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0605 audience_data
(cost=2.01..8.48 rows=3 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0605_index_audience_data_date (cost=0.00..2.01 rows=3
width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Heap Scan on audience_data_0606 audience_data
(cost=1706.00..483326.79 rows=214286 width=0)
Recheck Cond: (audience_data_date = '2006-06-06'::date)
-> Bitmap Index Scan on
audience_data_0606_index_audience_data_date (cost=0.00..1706.00
rows=214286 width=0)
Index Cond: (audience_data_date = '2006-06-06'::date)
(52 rows)

>
>
> --
> Atentamente,
> Jaime Casanova
> (DBA: DataBase Aniquilator ;)
>

--
Jhon Carrillo
DBA / Software Engineer
Caracas-Venezuela