pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery problem
9+ messages / 5 participants
[nested] [flat]
* Query problem
@ 2003-07-27 11:49 pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 1 reply; 9+ messages in thread
From: pginfo @ 2003-07-27 11:49 UTC (permalink / raw)
To: pgsql-performance
Hi ,
I am working to migrate a oracle application to pg.
I am using pg 7.3.3 on Dual PIII, 2 GB RAM,linux RedHat 7.3.
One of my selects is working much slower as in oracle.
In this example I am using not many rows in tables.
For all the joins I have indexes.
All IDS or IDS_xxx are name.
Pls if it is possible poit me how to fix this problem.
I send the query and the explai analyze.
I have ran vacuum analyze full on db.
Many thanks,
ivan.
explain analyze select O.IDS as oids,O.IDS_MDL_MDF_VOL as
ids_mmv,M.MNAME AS MODDELNAME,M.KOD AS MODELKOD,O.IDS_COLOR,COL.MNAME
AS COLORNAME,COL.KOD AS COLORKOD, TT.IDS AS LOCIDS,TT.MNAME AS LOCNAME,
TT.KOD AS LOC_KOD ,O.IDS_DOSTAV,DST.MNAME AS DOSTAVNAME,
O.IDS_PROIZV,PRZ.MNAME as
PROIZVNAME,O.CHASSI,O.CHASSI_ACC,O.DVIGATEL,O.ORDER_NUM,O.ORDER_DATE,O.DOG_OR_FREE,
O.NALICHEN,O.DATE_PROIZV, O.DATE_IN,O.ALI,O.DATE_ALI,
O.PRICE_PAY,O.PRICE_PAY_VAL,
O.START_DATE,O.DAYS,O.END_DATE,O.COMENTAR,O.IDS_AUTOVOZ,AWT.MNAME AS
AUTOVNAME,
O.SVERKA,O.NEW_OLD,O.KM,O.START_DATE_REZ,O.END_DATE_REZ,O.IDS_SLUJITEL,SLU.KOD,NULL
AS CT_IDS, NULL AS C_NUM, O.DATE_ALI2, NULL AS C_STATE, 0 AS DAMAGE,
O.REG_NUMBER AS CARREGNUMBER,O.DATE_REG AS CARREGDATE,O.GARTYPE,2002 AS
GODINA,O.COMENTAR1, O.IDS_COMBOPT,CB.KOD AS
IDS_COMBOPT_KOD,O.REF_BG,O.DAM,O.OBEM, O.IDS_TAPICERII,TAP.KOD AS
IDS_TAPICERII_KOD,TAP.MNAME AS
IDS_TAPICERII_NAME,O.PAPKA_N,O.CEDMICAPR, O.RADIO_KOD AS
RADIO_KOD,O.KEY_KOD AS KEY_KOD,O.ALARM_KOD AS ALARM_KOD,O.BOLT_KOD AS
BOLT_KOD,M.MOST_PS, NULL AS IDS_KLIENT , NULL AS KlientName ,O.TALON_N
AS talonN,O.STATEMODIFY AS STATEMOD,O.MESTA AS MESTA,O.CENA_COLOR AS
CENA_COL,O.CENA_TAP AS CENA_TAP,M.CENA_PROD AS
MCENA_PROD,M.CENA_PROD_VAL AS
MCENA_PROD_VAL,O.CENA_MDL,O.MESTA_MDL,O.CENA_COLOR_VAL,O.CENA_TAP_VAL,O.CENA_MDL_VAL,O.VIRTUALEN,M.IDS_GRUPA,COL.MNAME_1
AS COLMNAME1,O.DATE_PLAN_P,O.KM_PLAN_P from A_COLORS COL, A_MDL_MDF_VOL
M ,A_LOCATIONS TT, A_CARS O left outer join A_SLUJITELI SLU
ON(O.IDS_SLUJITEL=SLU.IDS) left outer join A_AUTOVOZ AWT
ON(O.IDS_AUTOVOZ=AWT.IDS) left outer join A_COMBOPT CB
ON(O.IDS_COMBOPT=CB.IDS) left outer join A_TAPICERII TAP
ON(O.IDS_TAPICERII=TAP.IDS) left outer join A_KLIENTI DST ON(
O.IDS_DOSTAV=DST.IDS) left outer join A_KLIENTI PRZ ON( O.IDS_PROIZV =
PRZ.IDS) ,A_CH_CAR CHT WHERE O.IDS_LOCATION=TT.IDS AND
O.IDS_MDL_MDF_VOL=M.IDS AND O.IDS_COLOR=COL.IDS AND CHT.IDS=O.IDS AND
CHT.INSTIME=1059300812726 AND CHT.SES=1059300377005 and O.DOG_OR_FREE
IN(0,2,3) ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=138.54..142.57 rows=2 width=2051) (actual
time=286.17..286.29 rows=2 loops=1)
Hash Cond: ("outer".ids_location = "inner".ids)
-> Hash Join (cost=137.42..141.40 rows=2 width=1971) (actual
time=285.95..286.02 rows=2 loops=1)
Hash Cond: ("outer".ids = "inner".ids_color)
-> Seq Scan on a_colors col (cost=0.00..3.12 rows=112
width=101) (actual time=0.01..0.30 rows=112 loops=1)
-> Hash (cost=137.41..137.41 rows=2 width=1870) (actual
time=285.43..285.43 rows=0 loops=1)
-> Hash Join (cost=134.88..137.41 rows=2 width=1870)
(actual time=285.12..285.42 rows=2 loops=1)
Hash Cond: ("outer".ids = "inner".ids_mdl_mdf_vol)
-> Seq Scan on a_mdl_mdf_vol m (cost=0.00..2.34
rows=34 width=189) (actual time=0.03..0.21 rows=34 loops=1)
-> Hash (cost=134.88..134.88 rows=2 width=1681)
(actual time=284.98..284.98 rows=0 loops=1)
-> Hash Join (cost=10.76..134.88 rows=2
width=1681) (actual time=189.62..284.97 rows=2 loops=1)
Hash Cond: ("outer".ids = "inner".ids)
-> Hash Join (cost=9.73..128.72
rows=1019 width=1617) (actual time=1.58..283.39 rows=1023 loops=1)
Hash Cond: ("outer".ids_proizv =
"inner".ids)
-> Hash Join (cost=7.50..108.66
rows=1019 width=1545) (actual time=1.34..234.05 rows=1023 loops=1)
Hash Cond:
("outer".ids_dostav = "inner".ids)
-> Hash Join
(cost=5.28..88.60 rows=1019 width=1473) (actual time=1.12..188.41
rows=1023 loops=1)
Hash Cond:
("outer".ids_tapicerii = "inner".ids)
-> Hash Join
(cost=2.40..67.89 rows=1019 width=1372) (actual time=0.68..145.58
rows=1023 loops=1)
Hash Cond:
("outer".ids_combopt = "inner".ids)
-> Hash Join
(cost=1.09..46.19 rows=1019 width=1301) (actual time=0.45..106.88
rows=1023 loops=1)
Hash
Cond: ("outer".ids_autovoz = "inner".ids)
-> Hash
Join (cost=1.09..41.03 rows=1019 width=1189) (actual time=0.31..72.28
rows=1023 loops=1)
Hash Cond: ("outer".ids_slujitel = "inner".ids)
->
Index Scan using i_cars_dog_or_free on a_cars o (cost=0.00..22.11
rows=1019 width=1119) (actual time=0.12..37.41 rows=1023 loops=1)
Filter: ((dog_or_free = 0) OR (dog_or_free = 2) OR (dog_or_free = 3))
->
Hash (cost=1.07..1.07 rows=7 width=70) (actual time=0.04..0.04 rows=0
loops=1)
-> Seq Scan on a_slujiteli slu (cost=0.00..1.07 rows=7 width=70)
(actual time=0.01..0.03 rows=7 loops=1)
-> Hash
(cost=0.00..0.00 rows=1 width=112) (actual time=0.00..0.00 rows=0
loops=1)
->
Seq Scan on a_autovoz awt (cost=0.00..0.00 rows=1 width=112) (actual
time=0.00..0.00 rows=0 loops=1)
-> Hash
(cost=1.25..1.25 rows=25 width=71) (actual time=0.09..0.09 rows=0
loops=1)
-> Seq
Scan on a_combopt cb (cost=0.00..1.25 rows=25 width=71) (actual
time=0.01..0.06 rows=25 loops=1)
-> Hash
(cost=2.70..2.70 rows=70 width=101) (actual time=0.29..0.29 rows=0
loops=1)
-> Seq Scan on
a_tapicerii tap (cost=0.00..2.70 rows=70 width=101) (actual
time=0.01..0.17 rows=70 loops=1)
-> Hash (cost=2.18..2.18
rows=18 width=72) (actual time=0.06..0.06 rows=0 loops=1)
-> Seq Scan on
a_klienti dst (cost=0.00..2.18 rows=18 width=72) (actual
time=0.01..0.03 rows=18 loops=1)
-> Hash (cost=2.18..2.18
rows=18 width=72) (actual time=0.07..0.07 rows=0 loops=1)
-> Seq Scan on a_klienti
prz (cost=0.00..2.18 rows=18 width=72) (actual time=0.01..0.05 rows=18
loops=1)
-> Hash (cost=1.03..1.03 rows=2
width=64) (actual time=0.03..0.03 rows=0 loops=1)
-> Seq Scan on a_ch_car cht
(cost=0.00..1.03 rows=2 width=64) (actual time=0.02..0.03 rows=2
loops=1)
Filter: ((instime =
1059300812726::bigint) AND (ses = 1059300377005::bigint))
-> Hash (cost=1.10..1.10 rows=10 width=80) (actual time=0.07..0.07
rows=0 loops=1)
-> Seq Scan on a_locations tt (cost=0.00..1.10 rows=10
width=80) (actual time=0.03..0.05 rows=10 loops=1)
Total runtime: 287.61 msec
(44 rows)
Time: 301.36 ms
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Query problem
@ 2003-07-27 14:25 Rod Taylor <rbt@rbt.ca>
parent: pginfo <pginfo@t1.unisoftbg.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rod Taylor @ 2003-07-27 14:25 UTC (permalink / raw)
To: pginfo <pginfo@t1.unisoftbg.com>; +Cc: pgsql-performance
Try re-arranging your join structure:
, A_CARS O
JOIN A_CH_CAR CHT ON (CHT.IDS=O.IDS)
left outer join A_SLUJITELI SLU ON(O.IDS_SLUJITEL=SLU.IDS)
left outer join A_AUTOVOZ AWT ON(O.IDS_AUTOVOZ=AWT.IDS)
left outer join A_COMBOPT CB ON(O.IDS_COMBOPT=CB.IDS)
left outer join A_TAPICERII TAP ON(O.IDS_TAPICERII=TAP.IDS)
left outer join A_KLIENTI DST ON(O.IDS_DOSTAV=DST.IDS)
left outer join A_KLIENTI PRZ ON(O.IDS_PROIZV = PRZ.IDS)
WHERE O.IDS_LOCATION=TT.IDS
AND O.IDS_MDL_MDF_VOL=M.IDS
AND CHT.INSTIME=1059300812726
AND CHT.SES=1059300377005
and O.DOG_OR_FREE IN(0,2,3);
I believe this will cause fewer rows to be used when hashing for the
left outer joins.
On Sun, 2003-07-27 at 07:49, pginfo wrote:
> Hi ,
>
> I am working to migrate a oracle application to pg.
> I am using pg 7.3.3 on Dual PIII, 2 GB RAM,linux RedHat 7.3.
>
> One of my selects is working much slower as in oracle.
> In this example I am using not many rows in tables.
> For all the joins I have indexes.
> All IDS or IDS_xxx are name.
>
> Pls if it is possible poit me how to fix this problem.
>
> I send the query and the explai analyze.
> I have ran vacuum analyze full on db.
>
> Many thanks,
> ivan.
>
> explain analyze select O.IDS as oids,O.IDS_MDL_MDF_VOL as
> ids_mmv,M.MNAME AS MODDELNAME,M.KOD AS MODELKOD,O.IDS_COLOR,COL.MNAME
> AS COLORNAME,COL.KOD AS COLORKOD, TT.IDS AS LOCIDS,TT.MNAME AS LOCNAME,
> TT.KOD AS LOC_KOD ,O.IDS_DOSTAV,DST.MNAME AS DOSTAVNAME,
> O.IDS_PROIZV,PRZ.MNAME as
> PROIZVNAME,O.CHASSI,O.CHASSI_ACC,O.DVIGATEL,O.ORDER_NUM,O.ORDER_DATE,O.DOG_OR_FREE,
> O.NALICHEN,O.DATE_PROIZV, O.DATE_IN,O.ALI,O.DATE_ALI,
> O.PRICE_PAY,O.PRICE_PAY_VAL,
> O.START_DATE,O.DAYS,O.END_DATE,O.COMENTAR,O.IDS_AUTOVOZ,AWT.MNAME AS
> AUTOVNAME,
> O.SVERKA,O.NEW_OLD,O.KM,O.START_DATE_REZ,O.END_DATE_REZ,O.IDS_SLUJITEL,SLU.KOD,NULL
> AS CT_IDS, NULL AS C_NUM, O.DATE_ALI2, NULL AS C_STATE, 0 AS DAMAGE,
> O.REG_NUMBER AS CARREGNUMBER,O.DATE_REG AS CARREGDATE,O.GARTYPE,2002 AS
> GODINA,O.COMENTAR1, O.IDS_COMBOPT,CB.KOD AS
> IDS_COMBOPT_KOD,O.REF_BG,O.DAM,O.OBEM, O.IDS_TAPICERII,TAP.KOD AS
> IDS_TAPICERII_KOD,TAP.MNAME AS
> IDS_TAPICERII_NAME,O.PAPKA_N,O.CEDMICAPR, O.RADIO_KOD AS
> RADIO_KOD,O.KEY_KOD AS KEY_KOD,O.ALARM_KOD AS ALARM_KOD,O.BOLT_KOD AS
> BOLT_KOD,M.MOST_PS, NULL AS IDS_KLIENT , NULL AS KlientName ,O.TALON_N
> AS talonN,O.STATEMODIFY AS STATEMOD,O.MESTA AS MESTA,O.CENA_COLOR AS
> CENA_COL,O.CENA_TAP AS CENA_TAP,M.CENA_PROD AS
> MCENA_PROD,M.CENA_PROD_VAL AS
> MCENA_PROD_VAL,O.CENA_MDL,O.MESTA_MDL,O.CENA_COLOR_VAL,O.CENA_TAP_VAL,O.CENA_MDL_VAL,O.VIRTUALEN,M.IDS_GRUPA,COL.MNAME_1
> AS COLMNAME1,O.DATE_PLAN_P,O.KM_PLAN_P from A_COLORS COL, A_MDL_MDF_VOL
> M ,A_LOCATIONS TT, A_CARS O left outer join A_SLUJITELI SLU
> ON(O.IDS_SLUJITEL=SLU.IDS) left outer join A_AUTOVOZ AWT
> ON(O.IDS_AUTOVOZ=AWT.IDS) left outer join A_COMBOPT CB
> ON(O.IDS_COMBOPT=CB.IDS) left outer join A_TAPICERII TAP
> ON(O.IDS_TAPICERII=TAP.IDS) left outer join A_KLIENTI DST ON(
> O.IDS_DOSTAV=DST.IDS) left outer join A_KLIENTI PRZ ON( O.IDS_PROIZV =
> PRZ.IDS) ,A_CH_CAR CHT WHERE O.IDS_LOCATION=TT.IDS AND
> O.IDS_MDL_MDF_VOL=M.IDS AND O.IDS_COLOR=COL.IDS AND CHT.IDS=O.IDS AND
> CHT.INSTIME=1059300812726 AND CHT.SES=1059300377005 and O.DOG_OR_FREE
> IN(0,2,3) ;
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Hash Join (cost=138.54..142.57 rows=2 width=2051) (actual
> time=286.17..286.29 rows=2 loops=1)
> Hash Cond: ("outer".ids_location = "inner".ids)
> -> Hash Join (cost=137.42..141.40 rows=2 width=1971) (actual
> time=285.95..286.02 rows=2 loops=1)
> Hash Cond: ("outer".ids = "inner".ids_color)
> -> Seq Scan on a_colors col (cost=0.00..3.12 rows=112
> width=101) (actual time=0.01..0.30 rows=112 loops=1)
> -> Hash (cost=137.41..137.41 rows=2 width=1870) (actual
> time=285.43..285.43 rows=0 loops=1)
> -> Hash Join (cost=134.88..137.41 rows=2 width=1870)
> (actual time=285.12..285.42 rows=2 loops=1)
> Hash Cond: ("outer".ids = "inner".ids_mdl_mdf_vol)
> -> Seq Scan on a_mdl_mdf_vol m (cost=0.00..2.34
> rows=34 width=189) (actual time=0.03..0.21 rows=34 loops=1)
> -> Hash (cost=134.88..134.88 rows=2 width=1681)
> (actual time=284.98..284.98 rows=0 loops=1)
> -> Hash Join (cost=10.76..134.88 rows=2
> width=1681) (actual time=189.62..284.97 rows=2 loops=1)
> Hash Cond: ("outer".ids = "inner".ids)
> -> Hash Join (cost=9.73..128.72
> rows=1019 width=1617) (actual time=1.58..283.39 rows=1023 loops=1)
> Hash Cond: ("outer".ids_proizv =
> "inner".ids)
> -> Hash Join (cost=7.50..108.66
> rows=1019 width=1545) (actual time=1.34..234.05 rows=1023 loops=1)
> Hash Cond:
> ("outer".ids_dostav = "inner".ids)
> -> Hash Join
> (cost=5.28..88.60 rows=1019 width=1473) (actual time=1.12..188.41
> rows=1023 loops=1)
> Hash Cond:
> ("outer".ids_tapicerii = "inner".ids)
> -> Hash Join
> (cost=2.40..67.89 rows=1019 width=1372) (actual time=0.68..145.58
> rows=1023 loops=1)
> Hash Cond:
> ("outer".ids_combopt = "inner".ids)
> -> Hash Join
> (cost=1.09..46.19 rows=1019 width=1301) (actual time=0.45..106.88
> rows=1023 loops=1)
> Hash
> Cond: ("outer".ids_autovoz = "inner".ids)
> -> Hash
> Join (cost=1.09..41.03 rows=1019 width=1189) (actual time=0.31..72.28
> rows=1023 loops=1)
>
> Hash Cond: ("outer".ids_slujitel = "inner".ids)
> ->
> Index Scan using i_cars_dog_or_free on a_cars o (cost=0.00..22.11
> rows=1019 width=1119) (actual time=0.12..37.41 rows=1023 loops=1)
>
> Filter: ((dog_or_free = 0) OR (dog_or_free = 2) OR (dog_or_free = 3))
> ->
> Hash (cost=1.07..1.07 rows=7 width=70) (actual time=0.04..0.04 rows=0
> loops=1)
>
> -> Seq Scan on a_slujiteli slu (cost=0.00..1.07 rows=7 width=70)
> (actual time=0.01..0.03 rows=7 loops=1)
> -> Hash
> (cost=0.00..0.00 rows=1 width=112) (actual time=0.00..0.00 rows=0
> loops=1)
> ->
> Seq Scan on a_autovoz awt (cost=0.00..0.00 rows=1 width=112) (actual
> time=0.00..0.00 rows=0 loops=1)
> -> Hash
> (cost=1.25..1.25 rows=25 width=71) (actual time=0.09..0.09 rows=0
> loops=1)
> -> Seq
> Scan on a_combopt cb (cost=0.00..1.25 rows=25 width=71) (actual
> time=0.01..0.06 rows=25 loops=1)
> -> Hash
> (cost=2.70..2.70 rows=70 width=101) (actual time=0.29..0.29 rows=0
> loops=1)
> -> Seq Scan on
> a_tapicerii tap (cost=0.00..2.70 rows=70 width=101) (actual
> time=0.01..0.17 rows=70 loops=1)
> -> Hash (cost=2.18..2.18
> rows=18 width=72) (actual time=0.06..0.06 rows=0 loops=1)
> -> Seq Scan on
> a_klienti dst (cost=0.00..2.18 rows=18 width=72) (actual
> time=0.01..0.03 rows=18 loops=1)
> -> Hash (cost=2.18..2.18
> rows=18 width=72) (actual time=0.07..0.07 rows=0 loops=1)
> -> Seq Scan on a_klienti
> prz (cost=0.00..2.18 rows=18 width=72) (actual time=0.01..0.05 rows=18
> loops=1)
> -> Hash (cost=1.03..1.03 rows=2
> width=64) (actual time=0.03..0.03 rows=0 loops=1)
> -> Seq Scan on a_ch_car cht
> (cost=0.00..1.03 rows=2 width=64) (actual time=0.02..0.03 rows=2
> loops=1)
> Filter: ((instime =
> 1059300812726::bigint) AND (ses = 1059300377005::bigint))
> -> Hash (cost=1.10..1.10 rows=10 width=80) (actual time=0.07..0.07
> rows=0 loops=1)
> -> Seq Scan on a_locations tt (cost=0.00..1.10 rows=10
> width=80) (actual time=0.03..0.05 rows=10 loops=1)
> Total runtime: 287.61 msec
> (44 rows)
>
> Time: 301.36 ms
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* query problem
@ 2004-10-13 09:21 Robin Ericsson <robin.ericsson@profecta.se>
0 siblings, 2 replies; 9+ messages in thread
From: Robin Ericsson @ 2004-10-13 09:21 UTC (permalink / raw)
To: pgsql-performance
Hi,
I sent this to general earlier but I was redirected to performance.
The query have been running ok for quite some time, but after I did a
vacuum on the database, it's very very slow. This IN-query is only 2
ids. Before the problem that in was a subselect-query returning around
6-7 ids. The tables included in the query are described in database.txt.
status=# select count(id) from data;
count
---------
1577621
(1 row)
status=# select count(data_id) from data_values;
count
---------
9680931
(1 row)
I did run a new explain analyze on the query and found the attached
result. The obvious problem I see is a full index scan in
"idx_dv_data_id". I tried dropping and adding the index again, thats why
is't called "idx_data_values_data_id" in the dump.
status=# EXPLAIN ANALYZE
status-# SELECT
status-# data.entered,
status-# data.machine_id,
status-# datatemplate_intervals.template_id,
status-# data_values.value
status-# FROM
status-# data, data_values, datatemplate_intervals
status-# WHERE
status-# datatemplate_intervals.id = data_values.template_id AND
status-# data_values.data_id = data.id AND
status-# data.machine_id IN (2,3) AND
status-# current_timestamp::timestamp - interval '60 seconds' <
data.entered;
Regards,
Robin
--
Robin Ericsson <robin.ericsson@profecta.se>
Profecta HB
Attachments:
explain2.txt (text/plain, 1.2K) download
Hash Join (cost=28646.01..274260.15 rows=555706 width=24) (actual time=102323.087..102323.196 rows=5 loops=1)
Hash Cond: ("outer".template_id = "inner".id)
-> Merge Join (cost=28644.09..265922.62 rows=555706 width=24) (actual time=102322.632..102322.709 rows=5 loops=1)
Merge Cond: ("outer".data_id = "inner".id)
-> Index Scan using idx_dv_data_id on data_values (cost=0.00..205034.19 rows=9580032 width=16) (actual time=17.503..86263.130
rows=9596747 loops=1)
-> Sort (cost=28644.09..28870.83 rows=90697 width=16) (actual time=0.829..0.835 rows=1 loops=1)
Sort Key: data.id
-> Index Scan using idx_d_entered on data (cost=0.00..20202.81 rows=90697 width=16) (actual time=0.146..0.185 rows=1 loops=1)
Index Cond: (((('now'::text)::timestamp(6) with time zone)::timestamp without time zone - '00:01:00'::interval) < entered)
Filter: ((machine_id = 2) OR (machine_id = 3))
-> Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382 rows=0 loops=1)
-> Seq Scan on datatemplate_intervals (cost=0.00..1.74 rows=74 width=8) (actual time=0.024..0.250 rows=74 loops=1)
Total runtime: 102323.491 ms
(13 rows)
database.txt (text/plain, 2.6K) download
status=# \d data
Table "public.data"
Column | Type | Modifiers
------------+-----------------------------+--------------------------------------------
id | integer | not null default nextval('data_seq'::text)
updated | timestamp without time zone |
entered | timestamp without time zone |
machine_id | integer |
datas | character varying(512)[] |
Indexes:
"data_pkey" primary key, btree (id)
"idx_d_entered" btree (entered)
"idx_d_machine_id" btree (machine_id)
Foreign-key constraints:
"machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id)
Triggers:
data_datestamp BEFORE INSERT OR UPDATE ON data FOR EACH ROW EXECUTE PROCEDURE datestamp_e()
status=# \d data_values
Table "public.data_values"
Column | Type | Modifiers
-------------+-----------------------------+-----------
updated | timestamp without time zone |
entered | timestamp without time zone |
data_id | integer |
template_id | integer |
value | character varying(512) |
Indexes:
"idx_data_values_data_id" btree (data_id)
"idx_dv_template_id" btree (template_id)
Foreign-key constraints:
"data_id" FOREIGN KEY (data_id) REFERENCES data(id)
"template_id" FOREIGN KEY (template_id) REFERENCES datatemplate_intervals(id)
Triggers:
data_values_datestamp BEFORE INSERT OR UPDATE ON data_values FOR EACH ROW EXECUTE PROCEDURE
datestamp_e()
status=# \d datatemplate_intervals
Table "public.datatemplate_intervals"
Column | Type | Modifiers
-------------+-----------------------------+--------------------------------------------------------------
id | integer | not null default
nextval('datatemplate_intervals_seq'::text)
updated | timestamp without time zone |
entered | timestamp without time zone |
machine_id | integer |
template_id | integer |
interval | integer |
Indexes:
"datatemplate_intervals_pkey" primary key, btree (id)
"idx_di_machine_id" btree (machine_id)
"idx_di_template_id" btree (template_id)
Foreign-key constraints:
"machine_id" FOREIGN KEY (machine_id) REFERENCES machines(id)
"template_id" FOREIGN KEY (template_id) REFERENCES datatemplates(id)
Triggers:
datatemplate_intervals_datestamp BEFORE INSERT OR UPDATE ON datatemplate_intervals FOR EACH
ROW EXECUTE PROCEDURE datestamp_e()
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: query problem
@ 2004-10-13 14:37 ken <southerland@samsixedd.com>
parent: Robin Ericsson <robin.ericsson@profecta.se>
1 sibling, 0 replies; 9+ messages in thread
From: ken @ 2004-10-13 14:37 UTC (permalink / raw)
To: Robin Ericsson <robin.ericsson@profecta.se>; +Cc: pgsql-performance
On Wed, 2004-10-13 at 02:21, Robin Ericsson wrote:
> Hi,
>
> I sent this to general earlier but I was redirected to performance.
>
> The query have been running ok for quite some time, but after I did a
> vacuum on the database, it's very very slow.
Did you do a VACUUM FULL ANALYZE on the database or just a VACUUM? It
looks like your statistics in your query are all off which ANALYZE
should fix.
> This IN-query is only 2
> ids. Before the problem that in was a subselect-query returning around
> 6-7 ids. The tables included in the query are described in database.txt.
>
> status=# select count(id) from data;
> count
> ---------
> 1577621
> (1 row)
>
> status=# select count(data_id) from data_values;
> count
> ---------
> 9680931
> (1 row)
>
> I did run a new explain analyze on the query and found the attached
> result. The obvious problem I see is a full index scan in
> "idx_dv_data_id". I tried dropping and adding the index again, thats why
> is't called "idx_data_values_data_id" in the dump.
>
> status=# EXPLAIN ANALYZE
> status-# SELECT
> status-# data.entered,
> status-# data.machine_id,
> status-# datatemplate_intervals.template_id,
> status-# data_values.value
> status-# FROM
> status-# data, data_values, datatemplate_intervals
> status-# WHERE
> status-# datatemplate_intervals.id = data_values.template_id AND
> status-# data_values.data_id = data.id AND
> status-# data.machine_id IN (2,3) AND
> status-# current_timestamp::timestamp - interval '60 seconds' <
> data.entered;
>
>
>
> Regards,
> Robin
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: query problem
@ 2004-10-13 15:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robin Ericsson <robin.ericsson@profecta.se>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2004-10-13 15:03 UTC (permalink / raw)
To: Robin Ericsson <robin.ericsson@profecta.se>; +Cc: pgsql-performance
Robin Ericsson <robin.ericsson@profecta.se> writes:
> I sent this to general earlier but I was redirected to performance.
Actually, I think I suggested that you consult the pgsql-performance
archives, where this type of problem has been hashed out before.
See for instance this thread:
http://archives.postgresql.org/pgsql-performance/2004-07/msg00169.php
particularly
http://archives.postgresql.org/pgsql-performance/2004-07/msg00175.php
http://archives.postgresql.org/pgsql-performance/2004-07/msg00184.php
http://archives.postgresql.org/pgsql-performance/2004-07/msg00185.php
which show three different ways of getting the planner to do something
sane with an index range bound like "now() - interval".
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: query problem
@ 2004-10-13 16:01 Robin Ericsson <robin.ericsson@profecta.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Robin Ericsson @ 2004-10-13 16:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Wed, 2004-10-13 at 11:03 -0400, Tom Lane wrote:
> Robin Ericsson <robin.ericsson@profecta.se> writes:
> > I sent this to general earlier but I was redirected to performance.
>
> Actually, I think I suggested that you consult the pgsql-performance
> archives, where this type of problem has been hashed out before.
> See for instance this thread:
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00169.php
> particularly
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00175.php
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00184.php
> http://archives.postgresql.org/pgsql-performance/2004-07/msg00185.php
> which show three different ways of getting the planner to do something
> sane with an index range bound like "now() - interval".
Using exact timestamp makes the query go back as it should in speed (see
explain below). However I still have the problem using a stored
procedure or even using the "ago"-example from above.
regards,
Robin
status=# explain analyse
status-# SELECT
status-# data.entered,
status-# data.machine_id,
status-# datatemplate_intervals.template_id,
status-# data_values.value
status-# FROM
status-# data, data_values, datatemplate_intervals
status-# WHERE
status-# datatemplate_intervals.id =
data_values.template_id AND
status-# data_values.data_id = data.id AND
status-# data.machine_id IN (SELECT machine_id FROM
machine_group_xref WHERE group_id = 1) AND
status-# '2004-10-13 17:47:36.902062' < data.entered
status-# ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=3.09..481.28 rows=777 width=24) (actual
time=0.637..1.804 rows=57 loops=1)
Hash Cond: ("outer".template_id = "inner".id)
-> Nested Loop (cost=1.17..467.71 rows=776 width=24) (actual
time=0.212..1.012 rows=57 loops=1)
-> Hash IN Join (cost=1.17..9.56 rows=146 width=16) (actual
time=0.165..0.265 rows=9 loops=1)
Hash Cond: ("outer".machine_id = "inner".machine_id)
-> Index Scan using idx_d_entered on data
(cost=0.00..6.14 rows=159 width=16) (actual time=0.051..0.097 rows=10
loops=1)
Index Cond: ('2004-10-13
17:47:36.902062'::timestamp without time zone < entered)
-> Hash (cost=1.14..1.14 rows=11 width=4) (actual
time=0.076..0.076 rows=0 loops=1)
-> Seq Scan on machine_group_xref
(cost=0.00..1.14 rows=11 width=4) (actual time=0.017..0.054 rows=11
loops=1)
Filter: (group_id = 1)
-> Index Scan using idx_data_values_data_id on data_values
(cost=0.00..3.07 rows=5 width=16) (actual time=0.018..0.047 rows=6
loops=9)
Index Cond: (data_values.data_id = "outer".id)
-> Hash (cost=1.74..1.74 rows=74 width=8) (actual time=0.382..0.382
rows=0 loops=1)
-> Seq Scan on datatemplate_intervals (cost=0.00..1.74
rows=74 width=8) (actual time=0.024..0.248 rows=74 loops=1)
Total runtime: 2.145 ms
(15 rows)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] query problem
@ 2004-10-13 16:27 Robin Ericsson <robin.ericsson@profecta.se>
parent: Robin Ericsson <robin.ericsson@profecta.se>
0 siblings, 1 reply; 9+ messages in thread
From: Robin Ericsson @ 2004-10-13 16:27 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Wed, 2004-10-13 at 18:01 +0200, Robin Ericsson wrote:
> Using exact timestamp makes the query go back as it should in speed (see
> explain below). However I still have the problem using a stored
> procedure or even using the "ago"-example from above.
Well, changing ago() to use timestamp without time zone it goes ok in
the query. This query now takes ~2ms.
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref
WHERE group_id = 1) AND
ago('60 seconds') < data.entered
Using it in this procedure.
select * from get_current_machine_status('60 seconds', 1);
takes ~100s. Maybe there's some obvious wrong I do about it?
CREATE TYPE public.mstatus_holder AS
(entered timestamp,
machine_id int4,
template_id int4,
value varchar);
CREATE OR REPLACE FUNCTION public.get_current_machine_status(interval,
int4)
RETURNS SETOF mstatus_holder AS
'
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref WHERE
group_id = $2) AND
ago($1) < data.entered
'
LANGUAGE 'sql' VOLATILE;
Regards,
Robin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [PERFORM] query problem
@ 2004-10-13 16:36 Robin Ericsson <robin.ericsson@profecta.se>
parent: Robin Ericsson <robin.ericsson@profecta.se>
0 siblings, 0 replies; 9+ messages in thread
From: Robin Ericsson @ 2004-10-13 16:36 UTC (permalink / raw)
To: Postgres general mailing list <pgsql-general@postgresql.org>
Sorry, this should have been going to performance.
Regards,
Robin
^ permalink raw reply [nested|flat] 9+ messages in thread
* [Fwd: Re: [GENERAL] query problem]
@ 2004-10-13 16:36 Robin Ericsson <robin.ericsson@profecta.se>
0 siblings, 0 replies; 9+ messages in thread
From: Robin Ericsson @ 2004-10-13 16:36 UTC (permalink / raw)
To: pgsql-performance
Sent this to wrong list.
-------- Forwarded Message --------
From: Robin Ericsson <robin.ericsson@profecta.se>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] [PERFORM] query problem
Date: Wed, 13 Oct 2004 18:27:20 +0200
On Wed, 2004-10-13 at 18:01 +0200, Robin Ericsson wrote:
> Using exact timestamp makes the query go back as it should in speed (see
> explain below). However I still have the problem using a stored
> procedure or even using the "ago"-example from above.
Well, changing ago() to use timestamp without time zone it goes ok in
the query. This query now takes ~2ms.
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref
WHERE group_id = 1) AND
ago('60 seconds') < data.entered
Using it in this procedure.
select * from get_current_machine_status('60 seconds', 1);
takes ~100s. Maybe there's some obvious wrong I do about it?
CREATE TYPE public.mstatus_holder AS
(entered timestamp,
machine_id int4,
template_id int4,
value varchar);
CREATE OR REPLACE FUNCTION public.get_current_machine_status(interval,
int4)
RETURNS SETOF mstatus_holder AS
'
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref WHERE
group_id = $2) AND
ago($1) < data.entered
'
LANGUAGE 'sql' VOLATILE;
Regards,
Robin
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
--
Robin Ericsson <robin.ericsson@profecta.se>
Profecta HB
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-10-13 16:36 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-27 11:49 Query problem pginfo <pginfo@t1.unisoftbg.com>
2003-07-27 14:25 ` Rod Taylor <rbt@rbt.ca>
2004-10-13 09:21 query problem Robin Ericsson <robin.ericsson@profecta.se>
2004-10-13 14:37 ` Re: query problem ken <southerland@samsixedd.com>
2004-10-13 15:03 ` Re: query problem Tom Lane <tgl@sss.pgh.pa.us>
2004-10-13 16:01 ` Re: query problem Robin Ericsson <robin.ericsson@profecta.se>
2004-10-13 16:27 ` Re: [PERFORM] query problem Robin Ericsson <robin.ericsson@profecta.se>
2004-10-13 16:36 ` Re: [PERFORM] query problem Robin Ericsson <robin.ericsson@profecta.se>
2004-10-13 16:36 [Fwd: Re: [GENERAL] query problem] Robin Ericsson <robin.ericsson@profecta.se>
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