Hi,
I don't know what happend when I execute this query?
select * from llamada where fecha='20030127' and tipo=1 and tiempo>0 and
gwdes like '64.7.127.14%' order by hora desc;
It's very slow, it takes approximately 6 minutes to show me the results. I
saw that it's happend when I execute this types of queries "Select...gwdes
like '64.7.127.14%'.." or with field gworg.
But when i use others fields the time that it take is faster.
I used PostgreSQL 7.0.3 and I have only problem with this type query. I have
approximately 1904796 registers in this table "llamada".
I did (VACUUM VERBOSE ANALYZE llamada) and (REINDEX INDEX
gwdes_lla,gworg_lla) and the query follows slow.
this is the struct of my Table "llamada"
Attribute | Type | Modifier
-----------+-------------+----------
callid | varchar(40) | not null
fecha | date |
hora | time |
ani | varchar(25) |
dnis | varchar(25) |
tiempo | float8 |
tipo | smallint |
gworg | varchar(16) |
gwdes | varchar(16) |
Indices: ani_lla,callid_lla,dnis_lla,fecha_lla,gwdes_lla,gworg_lla,hora_lla, llamada_pkey,tiempo_lla,tipo_lla
This is the result of EXPLAIN:
hb=# explain select * from llamada where fecha='20030127' and tipo=1 and
tiempo>0 and gwdes like '64.7.127.14%' order by hora desc;
NOTICE: QUERY PLAN:
Sort (cost=4.98..4.98 rows=2 width=82) -> Index Scan using gwdes_lla on llamada (cost=0.00..4.97 rows=2
width=82)
And this is the EXPLAIN of other query:
hb=# explain select * from llamada where fecha='20030127' and tipo=1 and
tiempo>0 and dnis like '54%' order by hora desc;
NOTICE: QUERY PLAN:
Sort (cost=21362.07..21362.07 rows=1 width=82) -> Index Scan using fecha_lla on llamada (cost=0.00..21362.06 rows=1
width=82)
And this is the result for VACUUM, for my table llamada:
hb=# VACUUM VERBOSE ANALYZE llamada;
NOTICE: --Relation llamada--
NOTICE: Pages 39167: Changed 0, reaped 113, Empty 0, New 0; Tup 1904796:
Vac 12, Keep/VTL 0/0, Crash 2, UnUsed 102, MinLen 143, MaxLen 176; Re-using:
Free/Avail. Space 16544/11084; EndEmpty/Avail. Pages 0/68. CPU 40.93s/23.27u
sec.
NOTICE: Index callid_lla: Pages 18051; Tuples 1904796: Deleted 12. CPU
18.00s/1.77u sec.
NOTICE: Index gwdes_lla: Pages 7095; Tuples 1904796: Deleted 0. CPU
10.69s/1.86u sec.
NOTICE: Index gworg_lla: Pages 6696; Tuples 1904796: Deleted 0. CPU
12.69s/1.69u sec.
NOTICE: Index tipo_lla: Pages 3756; Tuples 1904796: Deleted 11. CPU
4.44s/1.80u sec.
NOTICE: Index tiempo_lla: Pages 5016; Tuples 1904796: Deleted 10. CPU
5.96s/1.59u sec.
NOTICE: Index dnis_lla: Pages 10013; Tuples 1904796: Deleted 10. CPU
2.95s/2.00u sec.
NOTICE: Index ani_lla: Pages 7895; Tuples 1904796: Deleted 10. CPU
4.64s/1.86u sec.
NOTICE: Index hora_lla: Pages 5731; Tuples 1904796: Deleted 10. CPU
1.48s/1.76u sec.
NOTICE: Index fecha_lla: Pages 3831; Tuples 1904796: Deleted 10. CPU
4.04s/1.72u sec.
NOTICE: Index llamada_pkey: Pages 23446; Tuples 1904796: Deleted 10. CPU
8.25s/2.13u sec.
NOTICE: Rel llamada: Pages: 39167 --> 39167; Tuple(s) moved: 11. CPU
12.46s/0.36u sec.
NOTICE: Index callid_lla: Pages 18051; Tuples 1904796: Deleted 11. CPU
16.85s/1.31u sec.
NOTICE: Index gwdes_lla: Pages 7095; Tuples 1904796: Deleted 11. CPU
11.47s/1.26u sec.
NOTICE: Index gworg_lla: Pages 6696; Tuples 1904796: Deleted 11. CPU
4.37s/1.22u sec.
NOTICE: Index tipo_lla: Pages 3756; Tuples 1904796: Deleted 11. CPU
2.03s/1.26u sec.
NOTICE: Index tiempo_lla: Pages 5016; Tuples 1904796: Deleted 11. CPU
6.60s/1.25u sec.
NOTICE: Index dnis_lla: Pages 10013; Tuples 1904796: Deleted 11. CPU
3.00s/1.15u sec.
NOTICE: Index ani_lla: Pages 7895; Tuples 1904796: Deleted 11. CPU
4.70s/1.33u sec.
NOTICE: Index hora_lla: Pages 5731; Tuples 1904796: Deleted 11. CPU
1.71s/1.45u sec.
NOTICE: Index fecha_lla: Pages 3831; Tuples 1904796: Deleted 11. CPU
4.69s/1.24u sec.
NOTICE: Index llamada_pkey: Pages 23446; Tuples 1904796: Deleted 11. CPU
8.06s/1.32u sec.
If someone can help with this query I will thankfull,
Regards Ricardo
_________________________________________________________________
