Re: [PERFORM] Index-Advisor Tools

[Alexandre de Arruda Paes]

I will be very happy with a tool(or a stats table) that shows the most
searched values from a table(since a statistic reset).  i.e.:

table foo (id int, year int)

top 3 searched value for year field: 2017(500x), 2016(300x), 2015(55x)

With this info we can create partial indexes or do a table partitioning.



2017-10-31 15:25 GMT-02:00 Neto pr :

> Thanks for reply Antony.
> But from what I've read, HYPOPG only allows you to create hypothetical
> indexes, so the DBA can analyze if it brings benefits.
> What I would like is a tool that from a SQL Query indicates which indexes
> would be recommended to decrease the response time.
>
> Best Regards
> Neto
>
> 2017-10-31 15:19 GMT-02:00 Anthony Sotolongo :
>
>> Hi Neto,  maybe HypoPG
>> Can help you:
>>
>> https://github.com/dalibo/hypopg
>>
>> El 31 oct. 2017 2:13 PM, "Neto pr"  escribió:
>>
>>>
>>> Hello All I'm researching on Index-Advisor Tools to be applied in SQL
>>> queries. At first I found this: - EnterpriseDB -
>>> https://www.enterprisedb.com/docs/en/9.5/asguide/EDB_Postgre
>>> s_Advanced_Server_Guide.1.56.html Someone would know of other tools for
>>> this purpose. I'd appreciate it if you can help me.
>>>
>>> Best Regards
>>> Neto
>>>
>>
>

Re: [PERFORM] Fast HashJoin only after a cluster/recreate table

[Alexandre de Arruda Paes]

Hi David,

Thanks for the explanations. But I don't undestand why when I recreate the
table, the planner choose a best mode for sometime...

>I wonder what the planner would do if you pulled out the join to ES08T. If
that generates a better plan, then providing that es08tipdoc is the primary
key of that table, then you could just put a
> subquery in the SELECT clause to lookup the es08desdoc.

We have a problem with this approach. Actually, this querys are generated
by a framework and can't be 'rewrite'.
Can you think in another solution directly in DB (perhaps a partial index,
table partitioning, etc) ???

Best regards,

Alexandre



2016-04-01 10:17 GMT-03:00 David Rowley <david.row...@2ndquadrant.com>:

> On 1 April 2016 at 15:44, Alexandre de Arruda Paes <adald...@gmail.com>
> wrote:
>
>> In the query below, the planner choose an extreme slow mergejoin(380
>> seconds). 'Vacuum analyze' can't help.
>>
>
> Yeah, it appears that planner is estimating the WHERE clause on es09t
> quite badly, expecting just 1 row, but there's actually 1212 rows.  This
> seems to throw the whole plan decision out quite a bit, as, if you notice
> in the merge left join for t1.es09tipdoc = t2.es08tipdoc, it expect just 2
> rows to be present, therefore most likely thinks that it's not worth
> sorting those results on t1.es09tipdoc, t1.es09numdoc in order for it to
> match the known output order of Materialize node on the inner side of that
> join. Instead it assumes the Merge join will be good enough on just the
> es09tipdoc order, and just adds the other two columns as join filters
>
> ... and this seems to be what's killing the performance. This Merge Join
> constantly has to perform a mark/restore on the Materialize node. This why
> you're getting the insanely high "Rows Removed by Join Filter: 992875295",
> in other words the join filter throw away that many row combinations
> because they didn't match.
>
> This mark/restore is basically the rewind process that the merge join
> algorithm needs to do to match many to many rows. In actual fact, this
> rewind is pretty useless in this case as the GROUP BY subquery ensures that
> no duplicate values will make it into the inner side of that merge join.
> The planner is not currently smart enough to detect this.
>
> There are some patches currently pending for 9.6 which might help fix this
> problem in the future;
>
> 1. multivariate statistics; this might help the poor estimates on es09t.
> If this was available you could add statistics on es09codemp, es09datreq,
> which may well improve the estimate and cause the plan to change.
> https://commitfest.postgresql.org/9/450/
> 2. Unique joins. This tackles the problem a different way and allows the
> Merge Join algorithm to skip the restore with some new smarts that are
> added to the planner to detect when the inner side of the join can only
> produce, at most, a single row for each outer row.
> https://commitfest.postgresql.org/9/129/
>
> If you feel like compiling 9.6 devel from source and applying each of
> these patches independently and seeing if it helps...  Of course that does
> not solve your 9.4 production dilemma, but it may help evaluation of each
> of these two patches for 9.6 or beyond.
>
> I wonder what the planner would do if you pulled out the join to ES08T. If
> that generates a better plan, then providing that es08tipdoc is the primary
> key of that table, then you could just put a subquery in the SELECT clause
> to lookup the es08desdoc.
>
>
>>
>> QUERY PLAN
>>
>>
>> --
>>  Sort  (cost=289546.93..289546.94 rows=2 width=78) (actual
>> time=380405.796..380405.929 rows=2408 loops=1)
>>Sort Key: t1.es09numdoc, t1.es09tipdoc
>>Sort Method: quicksort  Memory: 435kB
>>Buffers: shared hit=82163
>>->  Merge Left Join  (cost=47.09..289546.92 rows=2 width=78) (actual
>> time=1133.077..380398.160 rows=2408 loops=1)
>>  Merge Cond: (t1.es09tipdoc = es09t1.es09tipdoc)
>>  Join Filter: ((es09t1.es09codemp = t1.es09codemp) AND
>> (es09t1.es09numdoc = t1.es09numdoc))
>>  Rows Removed by Join Filter: 992875295
>>  Buffers: shared hit=82163
>>  ->  Merge Left Join  (cost=46.53..49.29 rows=2 width=70) (actual
>> time=12.206..18.155 rows=2408 loops=1)
>>Merge Cond: (t1.es09tipdoc = t2.es08tipdoc)
>>Buffers: shared hit=6821
>>->  Sort  (cost=9.19..9.19 rows=2 width=44) (actual
>> time=11.611..12.248 rows=2408 loo

[PERFORM] Fast HashJoin only after a cluster/recreate table

[Alexandre de Arruda Paes]

Hi,

In the query below, the planner choose an extreme slow mergejoin(380
seconds). 'Vacuum analyze' can't help.
If I CLUSTER (or recreate) table ES09T1, the planner choose a faster
hashjoin (about 10 seconds). But, obviously, I can't do that with the users
connected.
After some time after cluster(generally in the same day), the problem
returns. Autovacuum is on, but the tables are vacuumed forced after
pg_dump, 3 times in a day (00:00 - 12:00 - 23:00).

Postgresql 9.4.5
128GB RAM/10xRAID10 SAS 15k
shared_buffers = 8GB

work_mem = 256MB

maintenance_work_mem = 16GB
random_page_cost = 2.0

effective_cache_size = 120GB


db=# explain (buffers,analyze) SELECT T1.es09item, T1.es09status,
T3.es09usuari, T3.es09datreq, T2.es08desdoc AS es09desdoc, T1.es09numdoc,
T1.es09tipdoc AS es09tipdoc, T1.es09codemp, COALESCE( T4.es09quatre, 0) AS
es09quatre FROM (((ES09T1 T1 LEFT JOIN ES08T T2 ON T2.es08tipdoc =
T1.es09tipdoc) LEFT JOIN ES09T T3 ON T3.es09codemp = T1.es09codemp AND
T3.es09tipdoc = T1.es09tipdoc AND T3.es09numdoc = T1.es09numdoc) LEFT JOIN
(SELECT COUNT(*) AS es09quatre, es09codemp, es09tipdoc, es09numdoc FROM
ES09T1 GROUP BY es09codemp, es09tipdoc, es09numdoc ) T4 ON T4.es09codemp =
T1.es09codemp AND T4.es09tipdoc = T1.es09tipdoc AND T4.es09numdoc =
T1.es09numdoc) WHERE (T1.es09codemp = 1) and (T3.es09datreq >= '2016-02-02'
and T3.es09datreq <= '2016-02-02') and (T3.es09usuari like
'') and (T1.es09tipdoc like '%') ORDER BY
T1.es09codemp, T1.es09numdoc DESC, T1.es09tipdoc;

  QUERY PLAN

--
 Sort  (cost=289546.93..289546.94 rows=2 width=78) (actual
time=380405.796..380405.929 rows=2408 loops=1)
   Sort Key: t1.es09numdoc, t1.es09tipdoc
   Sort Method: quicksort  Memory: 435kB
   Buffers: shared hit=82163
   ->  Merge Left Join  (cost=47.09..289546.92 rows=2 width=78) (actual
time=1133.077..380398.160 rows=2408 loops=1)
 Merge Cond: (t1.es09tipdoc = es09t1.es09tipdoc)
 Join Filter: ((es09t1.es09codemp = t1.es09codemp) AND
(es09t1.es09numdoc = t1.es09numdoc))
 Rows Removed by Join Filter: 992875295
 Buffers: shared hit=82163
 ->  Merge Left Join  (cost=46.53..49.29 rows=2 width=70) (actual
time=12.206..18.155 rows=2408 loops=1)
   Merge Cond: (t1.es09tipdoc = t2.es08tipdoc)
   Buffers: shared hit=6821
   ->  Sort  (cost=9.19..9.19 rows=2 width=44) (actual
time=11.611..12.248 rows=2408 loops=1)
 Sort Key: t1.es09tipdoc
 Sort Method: quicksort  Memory: 285kB
 Buffers: shared hit=6814
 ->  Nested Loop  (cost=1.11..9.18 rows=2 width=44)
(actual time=0.040..10.398 rows=2408 loops=1)
   Buffers: shared hit=6814
   ->  Index Scan using ad_es09t_1 on es09t t3
 (cost=0.56..4.58 rows=1 width=42) (actual time=0.020..0.687 rows=1212
loops=1)
 Index Cond: ((es09codemp = 1) AND
(es09datreq >= '2016-02-02'::date) AND (es09datreq <= '2016-02-02'::date))
 Filter: (es09usuari ~~
''::text)
 Buffers: shared hit=108
   ->  Index Scan using es09t1_pkey on es09t1 t1
 (cost=0.56..4.59 rows=1 width=19) (actual time=0.006..0.007 rows=2
loops=1212)
 Index Cond: ((es09codemp = 1) AND
(es09tipdoc = t3.es09tipdoc) AND (es09numdoc = t3.es09numdoc))
 Filter: (es09tipdoc ~~ '%'::text)
 Buffers: shared hit=6706
   ->  Sort  (cost=37.35..38.71 rows=547 width=32) (actual
time=0.592..2.206 rows=2919 loops=1)
 Sort Key: t2.es08tipdoc
 Sort Method: quicksort  Memory: 67kB
 Buffers: shared hit=7
 ->  Seq Scan on es08t t2  (cost=0.00..12.47 rows=547
width=32) (actual time=0.003..0.126 rows=547 loops=1)
   Buffers: shared hit=7
 ->  Materialize  (cost=0.56..287644.85 rows=716126 width=23)
(actual time=0.027..68577.800 rows=993087854 loops=1)
   Buffers: shared hit=75342
   ->  GroupAggregate  (cost=0.56..278693.28 rows=716126
width=15) (actual time=0.025..4242.453 rows=3607573 loops=1)
 Group Key: es09t1.es09codemp, es09t1.es09tipdoc,
es09t1.es09numdoc
 Buffers: shared hit=75342
 ->  Index Only Scan using es09t1_pkey on es09t1
 (cost=0.56..199919.49 rows=7161253 width=15) (actual time=0.016..1625.031
rows=7160921 loops=1)
   Index Cond: (es09codemp = 1)
   Heap Fetches: 51499
   Buffers: shared hit=75342
 Planning time: 50.129 ms
 Execution time: 

Re: [PERFORM] Slow HashAggregate/cache access

[Alexandre de Arruda Paes]

Hi,

Kevin:

Second machine config parameters:

shared_buffers = 8GB
work_mem = 1 GB (was 512MB)
maintenace_work_mem = 4 GB

#seq_page_cost = 1.0
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.005
#cpu_operator_cost = 0.0025

random_page_cost = 2.0
effective_cache_size = 110GB

I try to change from_collapse_limit, join_collapse_limit and io_con, w/o
success.

I create a database with this tables only, vaccum analyze them and test
with only my connection to postgresql.
Now we have another querys(all with aggregates) that the time is 15x - 20x
slower than Oracle and SQL Server.
All tables have indexes (btree) with fields in the where/order/group
parameters.

Maxim:

The developer is changing from a Desktop application (ODBC with Use
Declare/Fetch, 'single' querys with local summing and aggregation) for a
client/server web application (.NET, most querys with aggregate). Unfortunattly
we cant change this querys, but I will try your solution to see what
happens.

Take a look at another big query generated by the development tool.
Oracle/SQL Server runs the same query (with the same data but in a slow
machine) in about 2 seconds:


http://explain.depesz.com/s/wxq


Best regards,

Alexandre


2015-08-05 14:24 GMT-03:00 Kevin Grittner kgri...@ymail.com:

 Alexandre de Arruda Paes adald...@gmail.com wrote:

  We did the following tests:
 
  1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA
 disk,Core i5)
  2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores,
 SAS disks)

 That's only part of the information we would need to be able to
 give specific advice.  Please read this page:

 https://wiki.postgresql.org/wiki/SlowQueryQuestions

 One possibility is that you are running with the default
 configuration, rather than having tuned for the hardware.  You are
 very likely to need to adjust shared_buffers, effective_cache_size,
 work_mem, maintenance_work_mem, random_page_cost, cpu_tuple_cost,
 and (at least for the second machine) effective_io_concurrency.  If
 the queries have a lot of joins you may need to increase
 from_collapse_limit and/or join_collapse_limit.  You also may need
 to adjust [auto]vacuum and/or background writer settings.  Various
 OS settings may matter, too.

 To get a handle on all this, it might be worth looking for Greg
 Smith's book on PostgreSQL high performance.


 --
 Kevin Grittner
 EDB: http://www.enterprisedb.com
 The Enterprise PostgreSQL Company

Re: [PERFORM] Slow HashAggregate/cache access

[Alexandre de Arruda Paes]

Hi Andreas,

Same plan in 9.5, but the execution time was greater than 9.3 (maybe need
some tunning):

postgres@hw-prox01-fac:~/PG95$ /usr/PG95/bin/psql copro95 -p 5444
psql (9.5alpha1)
Type help for help.

copro95=# explain (analyze,buffers) SELECT
T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0) AS
fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS
fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc,
T1.fr02codigo,T1.fr01codemp FROMFR13T T1 LEFT JOIN FR02T T2 ON
T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN
FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT
JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
T5.fr13dtlanc = T1.fr13dtlanc)
WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc =
'01/05/2014') AND (T1.fr02codigo = '0' and T1.fr02codigo = '99')
AND (T1.fr13dtlanc = '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
T1.fr13dtlanc;
 QUERY
PLAN

 Nested Loop Left Join  (cost=30535.97..33949.17 rows=1 width=130) (actual
time=623.008..1029.130 rows=2 loops=1)
   Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 368
   Buffers: shared hit=21362
   -  Nested Loop Left Join  (cost=30529.83..33941.87 rows=1 width=98)
(actual time=622.761..1028.782 rows=2 loops=1)
 Join Filter: (t3.fr01codemp = t1.fr01codemp)
 Buffers: shared hit=21360
 -  Nested Loop Left Join  (cost=30529.70..33941.71 rows=1
width=87) (actual time=622.709..1028.699 rows=2 loops=1)
   Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 500202
   Buffers: shared hit=21356
   -  Nested Loop Left Join  (cost=0.70..2087.56 rows=1
width=23) (actual time=1.021..2.630 rows=2 loops=1)
 Buffers: shared hit=181
 -  Index Scan using ufr13t2 on fr13t t1
 (cost=0.42..2083.24 rows=1 width=19) (actual time=0.996..2.576 rows=2
loops=1)
   Index Cond: ((fr01codemp = '1'::smallint) AND
(fr13dtlanc = '2014-05-01'::date) AND (fr13dtlanc = '2014-05-31'::date))
   Filter: ((fr02codigo = '0'::numeric) AND
(fr02codigo = '99'::numeric) AND (fr13codpr = 60732))
   Rows Removed by Filter: 5621
   Buffers: shared hit=175
 -  Index Scan using fr02t_pkey on fr02t t2
 (cost=0.28..4.30 rows=1 width=12) (actual time=0.013..0.016 rows=1 loops=2)
   Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = '1'::smallint) AND (fr02codigo = t1.fr02codigo))
   Buffers: shared hit=6
   -  HashAggregate  (cost=30529.00..30996.70 rows=31180
width=21) (actual time=286.123..457.848 rows=250102 loops=2)
 Group Key: fr13t1.fr01codemp, fr13t1.fr02codigo,
fr13t1.fr13dtlanc
 Buffers: shared hit=21175
 -  Seq Scan on fr13t1  (cost=0.00..25072.50
rows=311800 width=21) (actual time=0.007..115.766 rows=311800 loops=1)
   Filter: (fr01codemp = '1'::smallint)
   Buffers: shared hit=21175
 -  Index Scan using fr09t_pkey on fr09t t3  (cost=0.14..0.16
rows=1 width=15) (actual time=0.026..0.027 rows=1 loops=2)
   Index Cond: ((fr01codemp = '1'::smallint) AND (fr09cod =
t2.fr09cod))
   Buffers: shared hit=4
   -  HashAggregate  (cost=6.14..6.50 rows=29 width=17) (actual
time=0.082..0.128 rows=184 loops=2)
 Group Key: fr13t3.fr01codemp, fr13t3.fr02codigo, fr13t3.fr13dtlanc
 Buffers: shared hit=2
 -  Seq Scan on fr13t3  (cost=0.00..4.30 rows=184 width=17)
(actual time=0.011..0.033 rows=184 loops=1)
   Filter: (fr01codemp = '1'::smallint)
   Buffers: shared hit=2
 Planning time: 2.394 ms
 Execution time: 1038.785 ms
(38 rows)

copro95=#


2015-08-05 16:55 GMT-03:00 Andreas Joseph Krogh andr...@visena.com:

 På onsdag 05. august 2015 kl. 20:25:25, skrev Maxim 

Re: [PERFORM] Slow HashAggregate/cache access

[Alexandre de Arruda Paes]

 Notice the seq scan on t1 instead of the index scan on t1_a_idx.

 A way around this is to manually push the predicate down into the subquery:

 explain select t2.a,s.sumv from (select a,sum(v) sumv from t1 where t1.a
 = 1 group by a) s inner join t2 on t2.a = s.a where t2.a = 1;
   QUERY PLAN

 ---
  Nested Loop  (cost=0.42..21.98 rows=1 width=12)
Join Filter: (t1.a = t2.a)
-  GroupAggregate  (cost=0.42..4.46 rows=1 width=8)
  Group Key: t1.a
  -  Index Scan using t1_a_idx on t1  (cost=0.42..4.44 rows=1
 width=8)
Index Cond: (a = 1)
-  Seq Scan on t2  (cost=0.00..17.50 rows=1 width=4)
  Filter: (a = 1)
 (8 rows)



Hi David,

You are right. If the subquery includes the same filters of the main select
(of the existing fields, sure), the times down to the floor (50 ms in the
first execution and *18* ms by cache. Superb! ):

(...)  (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 *WHERE (fr01codemp = '1' and fr13dtlanc = '01/05/2014') AND
(fr02codigo = '0' and fr02codigo = '99') AND (fr13dtlanc =
'31/05/2014') *GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
(...)


   QUERY PLAN

-
 Nested Loop Left Join  (cost=5770.32..7894.70 rows=1 width=130) (actual
time=13.715..18.366 rows=2 loops=1)
   Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 368
   Buffers: shared hit=5920
   -  Nested Loop Left Join  (cost=5764.18..7887.47 rows=1 width=98)
(actual time=13.529..18.108 rows=2 loops=1)
 Join Filter: (t3.fr01codemp = t1.fr01codemp)
 Buffers: shared hit=5918
 -  Nested Loop Left Join  (cost=5764.04..7887.30 rows=1 width=87)
(actual time=13.519..18.094 rows=2 loops=1)
   Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 11144
   Buffers: shared hit=5914
   -  Nested Loop Left Join  (cost=0.70..2098.42 rows=1
width=23) (actual time=0.796..2.071 rows=2 loops=1)
 Buffers: shared hit=181
 -  Index Scan using ufr13t2 on fr13t t1
 (cost=0.42..2094.11 rows=1 width=19) (actual time=0.787..2.054 rows=2
loops=1)
   Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc = '2014-05-01'::date) AND (fr13dtlanc = '2014-05-31'::date))
   Filter: ((fr02codigo = 0::numeric) AND
(fr02codigo = 99::numeric) AND (fr13codpr = 60732))
   Rows Removed by Filter: 5621
   Buffers: shared hit=175
 -  Index Scan using fr02t_pkey on fr02t t2
 (cost=0.28..4.30 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=2)
   Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
   Buffers: shared hit=6
   -  HashAggregate  (cost=5763.34..5770.15 rows=681 width=21)
(actual time=5.576..6.787 rows=5573 loops=2)
 Buffers: shared hit=5733
 -  Index Scan using ufr13t15 on fr13t1
 (cost=0.42..5644.31 rows=6802 width=21) (actual time=0.020..3.371
rows=7053 loops=1)
   Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc = '2014-05-01'::date) AND (fr13dtlanc = '2014-05-31'::date)
AND (fr02codigo = 0::numeric) AND (fr02codigo = 99::numeric))
   Buffers: shared hit=5733
 -  Index Scan using fr09t_pkey on fr09t t3  (cost=0.14..0.16
rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=2)
   Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
   Buffers: shared hit=4
   -  HashAggregate  (cost=6.14..6.43 rows=29 width=17) (actual
time=0.056..0.086 rows=184 loops=2)
 Buffers: shared hit=2
 -  Seq Scan on fr13t3  (cost=0.00..4.30 rows=184 width=17)
(actual time=0.003..0.027 rows=184 loops=1)
   Filter: (fr01codemp = 1::smallint)
   Buffers: shared hit=2
 Total runtime: 18.528 ms
(35 rows)


Tomorrow I will try to do the same with the other slow query, reporting
here.

Best regards,

Alexandre

Re: [PERFORM] Slow HashAggregate/cache access

[Alexandre de Arruda Paes]

 The query in question is likely performing badly because of this:

  -  Seq Scan on fr13t1  (cost=0.00..25072.50
 rows=311800 width=21) (actual time=0.007..115.766 rows=311800 loops=1)
Filter: (fr01codemp = '1'::smallint)
Buffers: shared hit=21175

 Just how selective is fr01codemp = '1'::smallint ? Is there an index on
 that column ?


Hi David,

In this case, fr13t1 has only value '1' in all fr01codemp:

copro95=# select fr01codemp,count(*) from fr13t1 group by fr01codemp;
 fr01codemp | count
+
  1 | 311800
(1 row)

Table public.fr13t1
   Column   |Type | Modifiers
+-+---
 fr01codemp | smallint| not null
 fr02codigo | numeric(10,0)   | not null
 fr13dtlanc | date| not null
 fr13sequen | smallint| not null
(...)
Indexes:
fr13t1_pkey PRIMARY KEY, btree (fr01codemp, fr02codigo, fr13dtlanc,
fr13sequen)
ifr13t1 btree (fr01codemp, fr07cod)
ifr13t12 btree (co18codord)
ifr13t14 btree (fr01codemp, fr52mot)
(...)

If planner needs to scan all table, can indexscan/indexonlyscan can take
any advantage ?

Besta regards,

Alexandre

[PERFORM] Slow HashAggregate/cache access

[Alexandre de Arruda Paes]

Hi,

First, sorry to compare Post with other database system, but I know nothing
about Oracle...

This customer have an application made with a framework thats generates the
SQL statements (so, We can't make any query optimizations) .

We did the following tests:

1) Postgresql 9.3 and Oracle 10 in a desktop machine(8 GB RAM, 1 SATA
disk,Core i5)
2) Postgresql 9.3 in a server + FC storage (128 GB RAM, Xeon 32 cores, SAS
disks)

In the first machine, postgresql takes from 20,000 to 40,000 ms to complete
the query and from 1,200 to 2,000 ms in the others runs. Oracle in this
machine takes 2,000ms in the first run and *70ms* using cache.

In the second machine, postgresql takes about 2,000ms in the first run and
about 800ms in the others. 11x slow than Oracle times, in a much more
powefull machine.

Bellow is the 2 explains in the second server:

database=# explain (analyze,buffers) SELECT
T1.fr13baixa,T1.fr13dtlanc,T2.fr02empfo,COALESCE( T4.fr13TotQtd, 0) AS
fr13TotQtd,T1.fr13codpr,T1.fr13categ,COALESCE( T5.fr13TotBx, 0) AS
fr13TotBx,COALESCE( T4.fr13VrTot, 0) AS fr13VrTot,T2.fr09cod, T3.fr09desc,
T1.fr02codigo,T1.fr01codemp FROMFR13T T1 LEFT JOIN FR02T T2 ON
T2.fr01codemp = T1.fr01codemp AND T2.fr02codigo = T1.fr02codigo)LEFT JOIN
FR09T T3 ON T3.fr01codemp = T1.fr01codemp AND T3.fr09cod = T2.fr09cod) LEFT
JOIN (SELECT SUM(fr13quant) AS fr13TotQtd, fr01codemp, fr02codigo,
fr13dtlanc, SUM(COALESCE( fr13quant, 0) * CAST(COALESCE( fr13preco, 0) AS
NUMERIC(18,10))) AS fr13VrTot
FROM FR13T1 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T4 ON
T4.fr01codemp = T1.fr01codemp AND T4.fr02codigo = T1.fr02codigo AND
T4.fr13dtlanc = T1.fr13dtlanc)
LEFT JOIN (SELECT SUM(fr13VrBx) AS fr13TotBx, fr01codemp, fr02codigo,
fr13dtlanc FROM FR13T3 GROUP BY fr01codemp, fr02codigo, fr13dtlanc ) T5 ON
T5.fr01codemp = T1.fr01codemp AND T5.fr02codigo = T1.fr02codigo AND
T5.fr13dtlanc = T1.fr13dtlanc)
WHERE (T1.fr01codemp = '1' and T1.fr13codpr = '60732' and T1.fr13dtlanc =
'01/05/2014') AND (T1.fr02codigo = '0' and T1.fr02codigo = '99')
AND (T1.fr13dtlanc = '31/05/2014') ORDER BY T1.fr01codemp, T1.fr13codpr,
T1.fr13dtlanc;
QUERY
PLAN
--
 Nested Loop Left Join  (cost=30535.97..33804.07 rows=1 width=130) (actual
time=1371.548..1728.058 rows=2 loops=1)
   Join Filter: ((fr13t3.fr01codemp = t1.fr01codemp) AND (fr13t3.fr02codigo
= t1.fr02codigo) AND (fr13t3.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 368
   Buffers: shared hit=95 read=21267
   -  Nested Loop Left Join  (cost=30529.83..33796.84 rows=1 width=98)
(actual time=1345.565..1701.990 rows=2 loops=1)
 Join Filter: (t3.fr01codemp = t1.fr01codemp)
 Buffers: shared hit=95 read=21265
 -  Nested Loop Left Join  (cost=30529.70..33796.67 rows=1
width=87) (actual time=1340.393..1696.793 rows=2 loops=1)
   Join Filter: ((fr13t1.fr01codemp = t1.fr01codemp) AND
(fr13t1.fr02codigo = t1.fr02codigo) AND (fr13t1.fr13dtlanc = t1.fr13dtlanc))
   Rows Removed by Join Filter: 500202
   Buffers: shared hit=93 read=21263
   -  Nested Loop Left Join  (cost=0.70..2098.42 rows=1
width=23) (actual time=36.424..66.841 rows=2 loops=1)
 Buffers: shared hit=93 read=88
 -  Index Scan using ufr13t2 on fr13t t1
 (cost=0.42..2094.11 rows=1 width=19) (actual time=27.518..57.910 rows=2
loops=1)
   Index Cond: ((fr01codemp = 1::smallint) AND
(fr13dtlanc = '2014-05-01'::date) AND (fr13dtlanc = '2014-05-31'::date))

   Filter: ((fr02codigo = 0::numeric) AND
(fr02codigo = 99::numeric) AND (fr13codpr = 60732))

   Rows Removed by Filter: 5621


   Buffers: shared hit=90 read=85


 -  Index Scan using fr02t_pkey on fr02t t2
 (cost=0.28..4.30 rows=1 width=12) (actual time=4.455..4.458 rows=1 loops=2)
   Index Cond: ((fr01codemp = t1.fr01codemp) AND
(fr01codemp = 1::smallint) AND (fr02codigo = t1.fr02codigo))
   Buffers: shared hit=3 read=3
   -  HashAggregate  (cost=30529.00..30840.80 rows=31180
width=21) (actual time=630.594..753.406 rows=250102 loops=2)
 Buffers: shared read=21175
 -  Seq Scan on fr13t1  (cost=0.00..25072.50
rows=311800 width=21) (actual time=6.354..720.037 rows=311800 loops=1)
   Filter: (fr01codemp = 1::smallint)
   Buffers: shared read=21175
 -  Index Scan using fr09t_pkey on fr09t t3  (cost=0.14..0.16
rows=1 width=15) (actual time=2.584..2.586 rows=1 loops=2)
   Index Cond: ((fr01codemp = 1::smallint) AND (fr09cod =
t2.fr09cod))
   Buffers: shared hit=2 

Re: Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

[Alexandre de Arruda Paes]

2010/8/21 Dimitri dimitrik...@gmail.com

 Great! - it's what I expected until now :-)
 but discussion in this thread put my mind in trouble :-))

 So, the advice for Alexandre here is just to check the age of the
 oldest running transaction and the last time when the table in
 question was modified.. - if modification time is older than the
 oldest transaction = we have a problem in PG.. Otherwise it works as
 expected to match MVCC.

 Rgds,
 -Dimitri


Unfortunately, the customer can't wait for the solution and the programmer
eliminated the
use of this table by using a in-memory array.

I understood that all transactions, touching this table or not, can affect
the ability of the vacuum to recover the dead tuples.
In my scenario, it's too bad because I have long transactions and I really
not know when I will recover this tuples.
And, like I sad, the table will become more slow every time.

Only for discussion: the CLUSTER command, in my little knowledge, is a
intrusive command that's cannot recover the dead tuples too.

Only TRUNCATE can do this job, but obviously is not applicable all the time.

Best regards,

Alexandre



 On 8/21/10, Scott Marlowe scott.marl...@gmail.com wrote:
  No, it means it can't clean rows that are younger than the oldest
  transaction currently in progress.  if you started a transaction 5
  hours ago, then all the dead tuples created in the last 5 hours are
  not recoverable.  Dead tuples created before that transaction are
  recoverable.  If you run transactions for days or weeks, then you're
  gonna have issues.
 
  On Sat, Aug 21, 2010 at 2:25 AM, Dimitri dimitrik...@gmail.com wrote:
  So, does it mean that VACUUM will never clean dead rows if you have a
  non-stop transactional activity in your PG database???... (24/7 OLTP
  for ex.)
 
  Rgds,
  -Dimitri
 
 
  On 8/19/10, Kevin Grittner kevin.gritt...@wicourts.gov wrote:
  Alexandre de Arruda Paes adald...@gmail.com wrote:
  2010/8/18 Tom Lane t...@sss.pgh.pa.us
 
  There's an open transaction somewhere that VACUUM is preserving
  the tuples for.  This transaction need not ever have touched the
  table, or ever intend to touch the table --- but VACUUM cannot
  know that, so it saves any tuples that the transaction might be
  entitled to see if it looked.
 
   carmen=# select * from vlocks where relname='tp93t'; select *
   from pg_stat_activity where usename='webpa';
 
  You keep on showing us only subsets of pg_stat_activity :-(
 
  select * from pg_stat_activity where usename='webpa';
 
  You keep on showing us only subsets of pg_stat_activity :-(
 
  *ANY* open transaction, including idle in transaction including
  transactions by other users in other databases will prevent vacuum
  from cleaning up rows, for the reasons Tom already gave you.
 
  What do you get from?:
 
  select * from pg_stat_activity where current_query  'IDLE'
order by xact_start limit 10;
 
  -Kevin
 
  --
  Sent via pgsql-performance mailing list
  (pgsql-performance@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-performance
 
 
  --
  Sent via pgsql-performance mailing list (
 pgsql-performance@postgresql.org)
  To make changes to your subscription:
  http://www.postgresql.org/mailpref/pgsql-performance
 
 
 
 
  --
  To understand recursion, one must first understand recursion.
 

Fwd: [PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

[Alexandre de Arruda Paes]

=# VACUUM verbose tp93t;
INFO:  vacuuming public.tp93t
INFO:  index tp93t_pkey now contains 5592 row versions in 103 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  tp93t: found 0 removable, 19336 nonremovable row versions in 4887
out of 4887 pages
DETAIL:  19126 dead row versions cannot be removed yet.


carmen=# VACUUM FULL verbose tp93t;
INFO:  vacuuming public.tp93t
INFO:  tp93t: found 0 removable, 19336 nonremovable row versions in 4887
pages
DETAIL:  19126 dead row versions cannot be removed yet.
Nonremovable row versions range from 1853 to 2029 bytes long.
There were 210 unused item pointers.
(...)







2010/8/17 Scott Marlowe scott.marl...@gmail.com

 On Tue, Aug 17, 2010 at 2:28 PM, Alexandre de Arruda Paes

 adald...@gmail.com wrote:

 So what do:
 select * from pg_stat_activity where current_query ilike '%transaction%';
 and
 select * from pg_stat_activity where now()-current_query  '1
 minute'::interval;
 say?

  And its the dead rows is growing:
 
  carmen=# VACUUM FULL verbose tp93t;

 You should really avoid vacuum full, and stick to vacuum (plain).  At
 least until you can get the tuples to be freed up.  Each time you run
 it you bloat your indexes.

  INFO:  vacuuming public.tp93t
  INFO:  tp93t: found 1309 removable, 313890 nonremovable row versions in
  78800 pages
  DETAIL:  312581 dead row versions cannot be removed yet.
  Nonremovable row versions range from 1845 to 2032 bytes long.
  There were 3014 unused item pointers.


 --
 To understand recursion, one must first understand recursion.

[PERFORM] Vacuum Full + Cluster + Vacuum full = non removable dead rows

[Alexandre de Arruda Paes]

Hi,

PG 8.4.4

I have an strange problem:

carmen=# VACUUM FULL verbose tp93t;
INFO:  vacuuming public.tp93t
INFO:  tp93t: found 0 removable, 71984 nonremovable row versions in 17996
pages
DETAIL:  70632 dead row versions cannot be removed yet.
Nonremovable row versions range from 1848 to 2032 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1523648 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  index tp93t_pkey now contains 71984 row versions in 868 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming pg_toast.pg_toast_24274
INFO:  pg_toast_24274: found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index pg_toast_24274_index now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

armen=# cluster tp93t;
CLUSTER

carmen=# VACUUM FULL verbose tp93t;
INFO:  vacuuming public.tp93t
INFO:  tp93t: found 0 removable, 71984 nonremovable row versions in 17996
pages
DETAIL:  70632 dead row versions cannot be removed yet.
Nonremovable row versions range from 1848 to 2032 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 1523648 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.03u sec elapsed 0.03 sec.
INFO:  index tp93t_pkey now contains 71984 row versions in 868 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming pg_toast.pg_toast_24274
INFO:  pg_toast_24274: found 0 removable, 0 nonremovable row versions in 0
pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  index pg_toast_24274_index now contains 0 row versions in 1 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

carmen=# select count(*) from tp93t;
 count
---
  1352
(1 row)


I did't see any transactions locking this table and I think that CLUSTER
will recreate the table.

This is a temporary table, with one DELETE, Some INSERTs and a lot of
UPDATES. And the UPDATES become slow and slow every time.
The only way to correct, is truncating the table.

Best regards,

Alexandre

[PERFORM] PG using index+filter instead only use index

[Alexandre de Arruda Paes]

Hi,

PostgreSQL 8.4.2 / default_statistics_target = 300

I have a strange problem for a bad choose of indexes.

client=# \d ct13t
 Table public.ct13t
   Column   | Type | Modifiers
+--+---
 ct12emp04  | integer  | not null
 ct03emp01  | integer  | not null
 ct03tradut | integer  | not null
 ct07emp01  | integer  | not null
 ct07c_cust | integer  | not null
 ct13dtlanc | date | not null
 ct12numlot | integer  | not null
 ct12numlan | integer  | not null
 ct13emptr1 | integer  |
 ct13tradu1 | integer  |
 ct13empcc1 | integer  |
 ct13ccust1 | integer  |
 ct13duoc   | character(1) |
Indexes:
    ct13t_pkey PRIMARY KEY, btree (ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan) CLUSTER
    ict13t1 btree (ct12emp04, ct12numlot, ct12numlan)
    ict13t2 btree (ct07emp01, ct07c_cust)
    ict13t3 btree (ct13empcc1, ct13ccust1)
    ict13t4 btree (ct03emp01, ct03tradut)
    ict13t5 btree (ct13emptr1, ct13tradu1)
    uct13t btree (ct12emp04, ct13dtlanc)


client=# explain analyze SELECT ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan FROM CT13T
WHERE ct12emp04 = '2' AND ct03emp01 = '2' AND ct03tradut = '60008' AND
ct07emp01 = '2' AND ct07c_cust = '0' AND ct13dtlanc =
'2005-01-28'::date AND ct12numlot = '82050128' AND ct12numlan = '123';

 QUERY PLAN
---
 Index Scan using ict13t2 on ct13t  (cost=0.00..5.69 rows=1 width=32)
(actual time=288.687..288.687 rows=0 loops=1)
   Index Cond: ((ct07emp01 = 2) AND (ct07c_cust = 0))
   Filter: ((ct12emp04 = 2) AND (ct03emp01 = 2) AND (ct03tradut =
60008) AND (ct13dtlanc = '2005-01-28'::date) AND (ct12numlot =
82050128) AND (ct12numlan = 123))
 Total runtime: 288.735 ms
(4 rows)

client=# create table ad_ct13t as select * from ct13t;
SELECT
client=# alter table ad_ct13t add primary key (ct12emp04, ct03emp01,
ct03tradut, ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot,
ct12numlan);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
ad_ct13t_pkey for table ad_ct13t
ALTER TABLE
client=# explain analyze SELECT ct12emp04, ct03emp01, ct03tradut,
ct07emp01, ct07c_cust, ct13dtlanc, ct12numlot, ct12numlan FROM
AD_CT13T WHERE ct12emp04 = '2' AND ct03emp01 = '2' AND ct03tradut =
'60008' AND ct07emp01 = '2' AND ct07c_cust = '0' AND ct13dtlanc =
'2005-01-28'::date AND ct12numlot = '82050128' AND ct12numlan = '123';

    QUERY PLAN

---
-
 Index Scan using ad_ct13t_pkey on ad_ct13t  (cost=0.00..5.66 rows=1
width=32) (actual time=0.090..0.090 rows=0 loops=1)
   Index Cond: ((ct12emp04 = 2) AND (ct03emp01 = 2) AND (ct03tradut =
60008) AND (ct07emp01 = 2) AND (ct07c_cust = 0) AND (ct13dtlanc =
'2005-01-28'::date) AND (ct12numlot = 82050128) AND (ct12numlan =
123))
 Total runtime: 0.146 ms
(3 rows)

My question: if the cost is exactly the same, why PG choose the index
ict13t2 on ct13t and apply a filter instead use the primary key ?
In one query, it's ok. But this routine execute millions times this query.

Thanks for any help,

Alexandre

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance