pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query 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