Re: [PERFORM] query problem
On Wed, 2004-10-13 at 11:03 -0400, Tom Lane wrote:
> Robin Ericsson <[EMAIL PROTECTED]> 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)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] query problem
Robin Ericsson <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] query problem
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 > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Query problem
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)
>
Re: RE : [PERFORM] Query problem
Hi Bruno, I think I have tunet it. Pg is working for most of my selects, but I have problem with this one. regards, ivan Bruno BAGUETTE wrote: > Hello, > > > 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. > > Have you tuned your postgresql.conf settings ? > > The PostgreSQL default settings are very low in order to allow > PostgreSQL to RUN on old machines and new machines. If you need > PERFORMANCE (which is quite logic), you must setup the postgresql.conf > file. > > Here's a nice article about the postgresql.conf file tuning : > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > > Hope this help ! :-) > > Cheers, > > --- > Bruno BAGUETTE - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
RE : [PERFORM] Query problem
Hello, > 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. Have you tuned your postgresql.conf settings ? The PostgreSQL default settings are very low in order to allow PostgreSQL to RUN on old machines and new machines. If you need PERFORMANCE (which is quite logic), you must setup the postgresql.conf file. Here's a nice article about the postgresql.conf file tuning : http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html Hope this help ! :-) Cheers, --- Bruno BAGUETTE - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
