From: | Martín Marqués <martin(at)bugs(dot)unl(dot)edu(dot)ar> |
---|---|
To: | Jaime Casanova <systemguards(at)yahoo(dot)com> |
Cc: | AyudaPostgres <pgsql-es-ayuda(at)postgresql(dot)org> |
Subject: | Re: Indice para columna date |
Date: | 2004-10-22 19:32:31 |
Message-ID: | 200410221632.31689.martin@bugs.unl.edu.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-es-ayuda |
El Vie 22 Oct 2004 13:20, Jaime Casanova escribió:
>
> Deja ver tabla de 860.069 registros, la consulta que
> das dice que va a retornar un estimado de 739.954
> registros (actual time=0.083..2543.389 rows=739954
> loops=1).
> Es decir va a retornar mas del 80% de la tabla el seq
> scan es definitivamente la mejor alternativa.
Mira vos!!! Y yo recién me doy cuenta. :-D
Pablo, acá van otros ejemplos (cambio el limite inferior de time_arch para que
agarre mas filas):
lismarch=> explain analyze select count(*) from archivo where time_arch >=
'10/2/2004';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=787.39..787.39 rows=1 width=0) (actual time=314.859..314.863
rows=1 loops=1)
-> Index Scan using prueba_time on archivo (cost=0.00..786.61 rows=313
width=0) (actual time=0.223..204.664 rows=1973 loops=1)
Index Cond: (time_arch >= '10/02/2004 00:00:00 ART'::timestamp with
time zone)
Total runtime: 325.306 ms
(4 filas)
lismarch=> explain analyze select count(*) from archivo where time_arch >=
'10/2/2001';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1466.96..1466.96 rows=1 width=0) (actual
time=762.004..762.008 rows=1 loops=1)
-> Seq Scan on archivo (cost=0.00..1446.30 rows=8264 width=0) (actual
time=0.175..569.133 rows=8264 loops=1)
Filter: (time_arch >= '10/02/2001 00:00:00 ART'::timestamp with time
zone)
Total runtime: 763.055 ms
(4 filas)
--
16:30:01 up 40 days, 6:48, 3 users, load average: 0.32, 0.34, 0.37
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Jose Joaquin Lorente Garcia | 2004-10-23 09:36:44 | Tamaño buffer/página de PostgresQL |
Previous Message | Ricardo Perez Lopez | 2004-10-22 18:03:04 | Re: '1 year' = '360 days' ???? |