pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with query
5+ messages / 3 participants
[nested] [flat]
* problem with query
@ 2002-09-19 19:11 Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ricardo Javier Aranibar León @ 2002-09-19 19:11 UTC (permalink / raw)
To: pgsql-sql
Hi list,
I need your help,
I need a table or view with this information that show down,I have two
tables "ticket" and "orden_respuesta" and I like this table.
Note, that some tti for example (TTI02000008) of ticket it isn't in the
table orden_respuesta, if somone can help me i will thank full
tti | numorden | tt | usuario | estado |
tipo
-------------+----------------------------+--------+---------+----------+----------
TTI02000006 | ORD02000046, ORD02000045 ..| 100029 | joroza | CERRADO |
ESPECIFI
TTI02000007 | ORD02000064, ORD02000061 | 100051 | joroza | REVISION |
ESPECIFI
TTI02000008 | (****** nothing ***** ) | 100049 | joroza | REVISION | IP
TTI02000009 | ORD02000040 | 56729 | joroza | CERRADO |
FACTURA
hb=# select tti, tt, usuario, estado, tipo from ticket;
tti | tt | usuario | estado | tipo
-------------+--------+---------+----------+-------------
TTI02000006 | 100029 | joroza | CERRADO | ESPECIFICO
TTI02000007 | 100051 | joroza | REVISION | ESPECIFICO
TTI02000008 | 100049 | joroza | REVISION | IP
TTI02000009 | 56729 | joroza | CERRADO | FACTURACION
hb=# select * from orden_respuesta ;
numorden | numtti
-------------+-------------
ORD02000046 | TTI02000006
ORD02000045 | TTI02000006
ORD02000044 | TTI02000006
ORD02000043 | TTI02000006
ORD02000042 | TTI02000006
ORD02000064 | TTI02000007
ORD02000061 | TTI02000007
ORD02000040 | TTI02000009
Regards,
Ricardo
_________________________________________________________________
Charle con sus amigos online usando MSN Messenger: http://messenger.msn.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* problem with query
@ 2002-09-19 19:48 Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ricardo Javier Aranibar León @ 2002-09-19 19:48 UTC (permalink / raw)
To: pgsql-sql; +Cc: ricardo_jal@linuxmail.org; jaranibar@heilsbergbol.com
Hi list,
First sorry if this message arrive in your box mail twice,
I need your colaboration,I like a table or view with this information from 2
tables "ticket" and "orden_respuesta".
Note, that the tti (TTI02000008) doesn't have links to "numorden" in the
table orden_respuesta, if someone can help me i will thankfull
numtti | numorden | tt | usuario | estado |
tipo
-------------+---------------------------+--------+---------+----------+-------------
TTI02000006 | ORD02000046, ORD02000045..| 100029 | joroza | CERRADO |
ESPECIFI
TTI02000007 | ORD02000064, ORD02000061..| 100051 | joroza | REVISION |
ESPECIFI
TTI02000008 | (***void***) | 100049 | joroza | REVISION | IP
TTI02000009 | ORD02000040 | 56729 | joroza | CERRADO |
FACTURA
//////////////
table ticket//
//////////////
hb=# select tti, tt, usuario, estado, tipo from ticket;
tti | tt | usuario | estado | tipo
-------------+--------+---------+----------+-------------
TTI02000006 | 100029 | joroza | CERRADO | ESPECIFICO
TTI02000007 | 100051 | joroza | REVISION | ESPECIFICO
TTI02000008 | 100049 | joroza | REVISION | IP
TTI02000009 | 56729 | joroza | CERRADO | FACTURACION
///////////////////////
table orden_respuesta//
///////////////////////
hb=# select * from orden_respuesta ;
numorden | numtti
-------------+-------------
ORD02000046 | TTI02000006
ORD02000045 | TTI02000006
ORD02000044 | TTI02000006
ORD02000043 | TTI02000006
ORD02000042 | TTI02000006
ORD02000064 | TTI02000007
ORD02000061 | TTI02000007
ORD02000040 | TTI02000009
regards,
Ricardo
_________________________________________________________________
Charle con sus amigos online usando MSN Messenger: http://messenger.msn.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Problem with query
@ 2003-01-28 21:00 Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ricardo Javier Aranibar León @ 2003-01-28 21:00 UTC (permalink / raw)
To: pgsql-sql; +Cc: ricardo_jal@linuxmail.org
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
_________________________________________________________________
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with query
@ 2003-01-29 02:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2003-01-29 02:18 UTC (permalink / raw)
To: Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>; +Cc: pgsql-sql; ricardo_jal@linuxmail.org
=?iso-8859-1?B?UmljYXJkbyBKYXZpZXIgQXJhbmliYXIgTGXzbg==?= <ricardo_jal@hotmail.com> writes:
> I used PostgreSQL 7.0.3
Please consider upgrading to something more recent. 7.0 predates a lot
of work we've done on query optimization ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with query
@ 2003-01-29 02:45 Josh Berkus <josh@agliodbs.com>
parent: Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-01-29 02:45 UTC (permalink / raw)
To: Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>; pgsql-sql; +Cc: ricardo_jal@linuxmail.org
Ricardo,
For future notice, there is another mailing list, PGSQL-PERFORMANCE,
devoted to questions like yours.
> 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.
Are you using 7.0.3 now, or something more current? I can't really
help you much with 7.0.3.
-Josh
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-01-29 02:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-19 19:11 problem with query Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2002-09-19 19:48 problem with query Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2003-01-28 21:00 Problem with query Ricardo Javier Aranibar León <ricardo_jal@hotmail.com>
2003-01-29 02:18 ` Re: Problem with query Tom Lane <tgl@sss.pgh.pa.us>
2003-01-29 02:45 ` Re: Problem with query Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox