Thread: Slow query

Hi List,

I need your help, I don't know what happend with my queries when I use this 
table "simon".When I like display the result the query is very slow and take 
five minutes or more.
The number of registers in simon is:2421580
and the other table "llamada" the number of registers is :1779741 but the 
time of my query is faster that when i run the query but using my table 
simon
The struct of my table "simon" is:
                           Table "simon"
Attribute |    Type     |                    Modifier
-----------+-------------+-------------------------------------------------
num       | integer     | not null default nextval('simon_num_seq'::text)
ip        | varchar(16) |
fecha     | date        |
hora      | time        |
tras      | smallint    |
reci      | smallint    |
perd      | smallint    |
min       | float4      |
avg       | float4      |
max       | float4      |
Indices: 
avg_si,fecha_si,hora_si,ip_si,max_si,min_si,perd_si,reci_si,simon_hora,        simon_pkey,tras_si

I run Explain and this is the result:
EXPLAIN SELECT * from simon where fecha='20030122' order by hora desc;
NOTICE:  QUERY PLAN:
Sort  (cost=57700.73..57700.73 rows=23682 width=46) ->  Seq Scan on simon  (cost=0.00..55591.05 rows=23682 width=46)

and when I run explain for another table this is te result:
EXPLAIN SELECT * from llamada where fecha='20030122' order by hora desc;
NOTICE:  QUERY PLAN:
Sort  (cost=41102.70..41102.70 rows=12413 width=82) ->  Index Scan using fecha_lla on llamada  (cost=0.00..39944.95
rows=12413
 
width=82)

This is the sintax when i created my index:
CREATE INDEX fecha_si ON simon USING btree(fecha);


If somebody can help me or tell me why my queries using the table simon are 
slow, I will thankfull

Regards,
Ricardo

PD:Sorry  for my grammar Enghish

_________________________________________________________________

Ricardo Javier Aranibar León wrote:
> I run Explain and this is the result:
> EXPLAIN SELECT * from simon where fecha='20030122' order by hora desc;
> NOTICE:  QUERY PLAN:
> Sort  (cost=57700.73..57700.73 rows=23682 width=46)
>  ->  Seq Scan on simon  (cost=0.00..55591.05 rows=23682 width=46)
> 
> and when I run explain for another table this is te result:
> EXPLAIN SELECT * from llamada where fecha='20030122' order by hora desc;
> NOTICE:  QUERY PLAN:
> Sort  (cost=41102.70..41102.70 rows=12413 width=82)
>  ->  Index Scan using fecha_lla on llamada  (cost=0.00..39944.95 
> rows=12413 width=82)

I'm not sure if there is much to help. You have a lots of rows in result -
23k rows and 12k rows. The problem is in output, not in query or indexing.

Do you really use such queries?
Anyway, did you vaccum analyze before testing?

Regards,
Tomasz Myrta