convert real to numeric.
Hi, Postresql version: 10.5 I need to convert an SQL field from real to numeric, but I’m getting a strange behavior. See the following query in preprod: select amount, amount::numeric, amount::numeric(16,4), amount::varchar::numeric from mytable where id = 32560545; Result: 17637.75, 17637.8, 17637.8000, 17637.75 As you can see, the conversion to ::numeric is truncating the number to just 1 decimal digit. Also we tried to change the schema definition of this table, from real to numeric, and the value was truncated. Is the ::varchar::numeric workaround a good option in your opinion? Any other ideas to fix this issue? Thank you,
Re: Eror while dropping a user
You can run this query to itendify the relations owned by the users you're
not allowed to drop, just replace ('',
'username2' ..'userN' ) with the your role names . Then, once you have
identified the tables/objecst change the owner like this:
ALTER TABLE OWNER TO ;
and try to drop the user again.
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v'
THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN
'f' THEN 'foreign table' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS
"Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname ||
E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM
pg_catalog.pg_attribute a
WHERE attrelid =
c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS
"Column privileges",
pg_catalog.array_to_string(ARRAY(
SELECT polname
|| CASE WHEN polcmd != '*' THEN
E' (' || polcmd || E'):'
ELSE E':'
END
|| CASE WHEN polqual IS NOT NULL THEN
E'\n (u): ' ||
pg_catalog.pg_get_expr(polqual, polrelid)
ELSE E''
END
|| CASE WHEN polwithcheck IS NOT NULL THEN
E'\n (c): ' ||
pg_catalog.pg_get_expr(polwithcheck, polrelid)
ELSE E''
END|| CASE WHEN polroles <> '{0}'
THEN
E'\n to: ' ||
pg_catalog.array_to_string(
ARRAY(
SELECT rolname
FROM pg_catalog.pg_roles
WHERE oid = ANY (polroles)
ORDER BY 1
), E', ')
ELSE E''
END
FROM pg_catalog.pg_policy pol
WHERE polrelid = c.oid), E'\n')
AS "Policies", ower_user.usename as "Object
Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
LEFT JOIN pg_catalog.pg_user ower_user on
(c.relowner = ower_user.usesysid)
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')
AND n.nspname !~ '^pg_' AND
pg_catalog.pg_table_is_visible(c.oid)
AND ower_user.usename in ('', 'username2');
On Fri, Aug 3, 2018 at 10:41 PM, Ertan Küçükoğlu <
ertan.kucuko...@1nar.com.tr> wrote:
> Hello,
>
> Using PostgreSQL 9.6.9 on Linux amd64 platform.
>
> I have two users that I cannot drop from the system. I revoked all
> permissions from tables, sequences, functions. They do not own any objects.
> My log lines are as following:
> 2018-08-03 23:24:03.897There are 2 users that needs to be removed from
> system.
> 2018-08-03 23:24:03.897Removing user previliges on tables: pars.test
> 2018-08-03 23:24:03.899Removing user previliges on sequences: pars.test
> 2018-08-03 23:24:03.900Removing user previliges on functions: pars.test
> 2018-08-03 23:24:03.900Dropping user itself: pars.test
> 2018-08-03 23:24:03.901Drop user failed: SQL Error: ERROR: role
> "pars.test" cannot be dropped because some objects depend on it
> AYRINTI: 1 object in database postgres
> 2018-08-03 23:24:03.901Removing user previliges on tables: pars.test2
> 2018-08-03 23:24:03.902Removing user previliges on sequences:
> pars.test2
> 2018-08-03 23:24:03.903Removing user previliges on functions:
> pars.test2
> 2018-08-03 23:24:03.903Dropping user itself: pars.test2
> 2018-08-03 23:24:03.904Drop user failed: SQL Error: ERROR: role
> "pars.test2" cannot be dropped because some objects depend on it
> AYRINTI: 1 object in database postgres
>
> I could not find any reference on postgres database for these users. I am
> not very good on database administration.
>
> Any help is appreciated.
>
> Thanks & regards,
> Ertan
>
>
>
>
>
>
Re: cache lookup failed for attribute 1 of relation XXXXXX
Thanks much, I'll keep my eyes open today night hoping it will not happen again. On Thu, Jul 19, 2018 at 5:39 PM, Tom Lane wrote: > [ please keep the list cc'd for the archives' sake ] > > Alessandro Aste writes: > > Hello Tom, thanks for your reply: > > SELECT * FROM pg_class WHERE OID = 2223152859 ; > > (0 rows) > > I'm not aware of any DDL at that time. > > Hm. Well, that OID was definitely there when pg_dump looked, and > it's not there now, so something changed --- though we can't prove > it changed concurrently. > > In any case, I'd bet that if we ran this to ground it would prove to be a > concurrent-DDL issue. pg_dump tries to protect itself against concurrent > DDL, but for assorted architectural reasons the protection is not 100%; > sometimes you can get odd failures like this, essentially due to "clock > skew" between pg_dump's view of the catalogs and the server's view of the > catalogs. As long as it works on retry, I wouldn't worry too much about > it. > > regards, tom lane >
cache lookup failed for attribute 1 of relation XXXXXX
Hi, we have a logical backup process that runs every night since 5+ years.
It is a logical backup we use to restore a non production environment. We
use pg_dump in parallel mode in directory format.
Postgres version is 9.6.6
Tonight schedule failed with the following error:
pg_dump: [archiver (db)] query failed: ERROR: cache lookup failed for
attribute 1 of relation 2223152859
pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname
AS indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef,
t.relnatts AS indnkeys, i.indkey, i.indisclustered, i.indisreplident,
t.relpages, c.contype, c.conname, c.condeferrable, c.condeferred,
c.tableoid AS contableoid, c.oid AS conoid,
pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname
FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS
tablespace, t.reloptions AS indreloptions FROM pg_catalog.pg_index i JOIN
pg_catalog.pg_class t ON (t.oid = i.indexrelid) LEFT JOIN
pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND i.indexrelid =
c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid =
'2223152859'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY
indexname
We attempted to run the backup manually the 2nd time just after a couple of
minutes and it suceeded with no issues, the restore of the non production
env suceeded too. Not sure what this error is though. Have never seen it
before.
Index with that indexrelid does not exists
SELECT * FROM pg_catalog.pg_index WHERE indexrelid = '2223152859';
(0 rows)
Any idea about what happened? In the server logs I see only one occurrence
of that error and it is related to the failed pg_dump:
2018-07-19 01:04:26 GMT [127.0.0.1(52498)] [50816]: [13-1]
db=cmdv3,user=postgres ERROR: cache lookup failed for attribute 1 of
relation 2223152859
Thanks much in advance
Re: Query to monitor index bloat
Thanks much, I'll check that out. I see the queries are 3 years old so I'm wondering if they still work for 9.6.x or 10 Il lun 16 lug 2018, 17:44 Adrien NAYRAT ha scritto: > On 07/16/2018 05:16 PM, Alessandro Aste wrote: > > Hello, I am trying to put togheter a query to monitor the index bloat > > for a database I maintain. > > Is there a "SQL" way to obtain bloated index ? I googled around but I > > found nothing working. > > > > I'm currently running 9.6 but I'm looking for something compatible with > > version 10 too. > > > > Thank you very much in advance, > > > > > > Alessandro. > > Hello, > > You should look at : https://github.com/ioguix/pgsql-bloat-estimation > > Regards, >
Query to monitor index bloat
Hello, I am trying to put togheter a query to monitor the index bloat for a database I maintain. Is there a "SQL" way to obtain bloated index ? I googled around but I found nothing working. I'm currently running 9.6 but I'm looking for something compatible with version 10 too. Thank you very much in advance, Alessandro.
Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on
Hello, any news ? Thank you, Alessandro. On Fri, Mar 23, 2018 at 8:22 PM, Alessandro Aste <alessandro.a...@gmail.com> wrote: > PS , in the meanwhile I discovered a 2nd workaround(beside disabling > parallel processing) . I added offset 0 to the subquery , and, according > to the documentation, “OFFSET 0 is the same as omitting the OFFSET clause” > - https://www.postgresql.org/docs/current/static/queries-limit.html > > cmd3dev=# show max_parallel_workers_per_gather ; > > *max_parallel_workers_per_gather* > > *-* > > *8* > > (1 row) > > > > cmd3dev=# \timing > > Timing is on. > > cmd3dev=# SELECT * FROM (SELECT seg.circuit_id AS id, > vendor_gtt_pop.vendor_id, CASE WHEN vendor_gtt_pop.push_to_gaa = 1 THEN 'Y' > END as gaa, pop.gii_circuitid AS pop_name, cst.label AS pop_status, > seg.a_company_name AS pop_location, seg.vendor_id AS pop_provider_id, > seg.vendor_name AS pop_provider, cs.address1 AS pop_street, cs.city AS > pop_city, cs.postal_code AS pop_postal_code, cs.state AS pop_state, > csc.code AS pop_country, cs.country_id AS pop_country_id FROM ( SELECT > c.gii_circuitid, max(so.id) AS service_order_id FROM service_order so > join circuit c on c.product_id=so.product_id join master_service_order mso > on mso.id=so.master_service_order_id WHERE NOT (so.ordertype_id = 2 AND > so.status <> 999) AND NOT (so.ordertype_id = 3 AND so.status <> 999) AND > c.status >= 20 AND c.status not in (160,999) AND mso.client_id=11615 AND > c.service_description=28 AND c.status!=160 GROUP BY c.gii_circuitid ) pop > JOIN service_order so ON so.id = pop.service_order_id left JOIN > client_site cs on cs.id=so.a_site_id left JOIN country csc on > csc.id=cs.country_id > JOIN circuit c ON so.product_id=c.product_id JOIN circuit_status cst ON > cst.id=c.status JOIN ( SELECT c.id AS circuit_id, sg.id AS segment_id, > c.pop_support_vendor_id AS vendor_id, v.name AS vendor_name, > sg.a_company_name FROM segment sg JOIN circuit_layout cl ON cl.segment_id = > sg.id AND cl.ordinal = 1 JOIN circuit c ON c.id = cl.circuit_id JOIN > vendor v ON v.id = c.pop_support_vendor_id ) seg ON seg.circuit_id = c.id > JOIN vendor_gtt_pop on vendor_gtt_pop.gtt_pop_id = seg.circuit_id offset 0) > foo where vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id desc LIMIT > 10; > >id | vendor_id | gaa | pop_name| pop_status | pop_location | > pop_provider_id | pop_provider | pop_street | pop_city | pop > > _postal_code | pop_state | pop_country | pop_country_id > > +---+-+---++ > --+-+--+ > -+--+ > > -+---+-+ > > 684807 | 12346 | Y | GTT/POP/LON1T | Active | LON1T > | 12288 | Telehouse UK | 14 Coriander Avenue | London | E14 > > 2AA | | GB |219 > > (1 row) > > > > *Time: 2245.073 ms (00:02.245)* > > > > On Fri, Mar 23, 2018 at 9:31 AM, Alessandro Aste < > alessandro.a...@gmail.com> wrote: > >> Tomas, I'm attaching a 4MB file with the perf report. Let me know if it >> gets blocked, I'll shrink it to the first 1000 lines. >> >> Thank you, >> >> Alessandro. >> >> On Thu, Mar 22, 2018 at 11:42 PM, Tomas Vondra < >> tomas.von...@2ndquadrant.com> wrote: >> >>> On 03/22/2018 11:29 PM, Alessandro Aste wrote: >>> > Thanks Tomas. We're currently building postgres from source. In order >>> to >>> > enable symbols, you want me to re-configure postres with >>> --enable-debug >>> > then run perf? >>> > >>> >>> Yes. >>> >>> regards >>> >>> -- >>> Tomas Vondra http://www.2ndQuadrant.com >>> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >>> >> >> >
Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on
PS , in the meanwhile I discovered a 2nd workaround(beside disabling parallel processing) . I added offset 0 to the subquery , and, according to the documentation, “OFFSET 0 is the same as omitting the OFFSET clause” - https://www.postgresql.org/docs/current/static/queries-limit.html cmd3dev=# show max_parallel_workers_per_gather ; *max_parallel_workers_per_gather* *-* *8* (1 row) cmd3dev=# \timing Timing is on. cmd3dev=# SELECT * FROM (SELECT seg.circuit_id AS id, vendor_gtt_pop.vendor_id, CASE WHEN vendor_gtt_pop.push_to_gaa = 1 THEN 'Y' END as gaa, pop.gii_circuitid AS pop_name, cst.label AS pop_status, seg.a_company_name AS pop_location, seg.vendor_id AS pop_provider_id, seg.vendor_name AS pop_provider, cs.address1 AS pop_street, cs.city AS pop_city, cs.postal_code AS pop_postal_code, cs.state AS pop_state, csc.code AS pop_country, cs.country_id AS pop_country_id FROM ( SELECT c.gii_circuitid, max(so.id) AS service_order_id FROM service_order so join circuit c on c.product_id=so.product_id join master_service_order mso on mso.id=so.master_service_order_id WHERE NOT (so.ordertype_id = 2 AND so.status <> 999) AND NOT (so.ordertype_id = 3 AND so.status <> 999) AND c.status >= 20 AND c.status not in (160,999) AND mso.client_id=11615 AND c.service_description=28 AND c.status!=160 GROUP BY c.gii_circuitid ) pop JOIN service_order so ON so.id = pop.service_order_id left JOIN client_site cs on cs.id=so.a_site_id left JOIN country csc on csc.id=cs.country_id JOIN circuit c ON so.product_id=c.product_id JOIN circuit_status cst ON cst.id=c.status JOIN ( SELECT c.id AS circuit_id, sg.id AS segment_id, c.pop_support_vendor_id AS vendor_id, v.name AS vendor_name, sg.a_company_name FROM segment sg JOIN circuit_layout cl ON cl.segment_id = sg.id AND cl.ordinal = 1 JOIN circuit c ON c.id = cl.circuit_id JOIN vendor v ON v.id = c.pop_support_vendor_id ) seg ON seg.circuit_id = c.id JOIN vendor_gtt_pop on vendor_gtt_pop.gtt_pop_id = seg.circuit_id offset 0) foo where vendor_id = 12346 AND (1=1) ORDER BY pop_name ASC,id desc LIMIT 10; id | vendor_id | gaa | pop_name| pop_status | pop_location | pop_provider_id | pop_provider | pop_street | pop_city | pop _postal_code | pop_state | pop_country | pop_country_id +---+-+---++--+-+--+-+--+ -+---+-+ 684807 | 12346 | Y | GTT/POP/LON1T | Active | LON1T | 12288 | Telehouse UK | 14 Coriander Avenue | London | E14 2AA | | GB |219 (1 row) *Time: 2245.073 ms (00:02.245)* On Fri, Mar 23, 2018 at 9:31 AM, Alessandro Aste <alessandro.a...@gmail.com> wrote: > Tomas, I'm attaching a 4MB file with the perf report. Let me know if it > gets blocked, I'll shrink it to the first 1000 lines. > > Thank you, > > Alessandro. > > On Thu, Mar 22, 2018 at 11:42 PM, Tomas Vondra < > tomas.von...@2ndquadrant.com> wrote: > >> On 03/22/2018 11:29 PM, Alessandro Aste wrote: >> > Thanks Tomas. We're currently building postgres from source. In order to >> > enable symbols, you want me to re-configure postres with --enable-debug >> > then run perf? >> > >> >> Yes. >> >> regards >> >> -- >> Tomas Vondra http://www.2ndQuadrant.com >> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >> > >
Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on
Thanks Tomas. We're currently building postgres from source. In order to enable symbols, you want me to re-configure postres with --enable-debug then run perf? Regards, On Thu, Mar 22, 2018 at 5:00 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > > > On 03/22/2018 11:33 AM, Alessandro Aste wrote: > > Tomas, thank you. This machine is abare metal server running only a > > staging postgresql 10.3 instance. Nobody is using it beside me. > > > > I'm attaching 4 files. > > > > every_30_seconds_top_stats_during_query.txt- this is a caputure of > > the top command every 30 seconds(more or less) for 10+ minutes while I'm > > running the query. Let me know if this helps to answere your question. > > EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt - > > query plan with full query and max_parallel_workers_per_gather force > > to 0. Full output. > > EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt - > > query plan with full query and default parellel processing settings. > > Full output. > > EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx - > > query plan of the query omitting the LIMIT clause and default parellel > > processing settings. Full output. > > > > OK. Looking at the top output, I see this: > >PID USER VIRT RES SHR S %CPU %MEMTIME+ COMMAND > 104880 postgres 30.8g 1.9g 1.9g R 92.0 1.5 15:15.60 postmaster > 111732 postgres 30.8g 476m 473m R 88.2 0.4 0:00.47 postmaster > 111730 postgres 30.8g 473m 470m R 86.4 0.4 0:00.46 postmaster > 111731 postgres 30.8g 476m 473m R 86.4 0.4 0:00.46 postmaster > 111733 postgres 30.8g 476m 473m R 86.4 0.4 0:00.46 postmaster > 111734 postgres 30.8g 476m 473m R 86.4 0.4 0:00.46 postmaster > 111728 root 15824 1912 828 R 3.8 0.0 0:00.04 top > > That means it certainly is not stuck, it's simply doing a lot of work on > CPU. The question is why and what it's doing ... > > Can you collect some CPU profiles using perf? There's a howto here: > > https://wiki.postgresql.org/wiki/Profiling_with_perf > > But in short - install perf, install debuginfo packages for postgres, > and then do > > perf record -a -g -s sleep 60 > > while running the query. Once the profile data is collected, do > > perf report > report.txt > > and share the report.txt with us (well, if it's very large you may need > to only cut the first 1000 lines or so). > > That should tell us in which functions most of the time is spent. That > will give us some hints, hopefully. > > > > > For what concerns the self-contained test case - I'll do my best to > > prepare it. > > > > Yeah, that would be helpful. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on
Tomas, thank you. This machine is abare metal server running only a staging postgresql 10.3 instance. Nobody is using it beside me. I'm attaching 4 files. every_30_seconds_top_stats_during_query.txt- this is a caputure of the top command every 30 seconds(more or less) for 10+ minutes while I'm running the query. Let me know if this helps to answere your question. EXPLAIN_WITH_LIMIT_AND_max_parallel_workers_per_gather_SET_TO_0.txt - query plan with full query and max_parallel_workers_per_gather force to 0. Full output. EXPLAIN_WITH_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.txt -query plan with full query and default parellel processing settings. Full output. EXPLAIN_WITHOUT_LIMIT_AND_DEFAULT_PARALLEL_PROCESSING_SETTINGS.tx - query plan of the query omitting the LIMIT clause and default parellel processing settings. Full output. For what concerns the self-contained test case - I'll do my best to prepare it. Thank you very much, please let me know if this answer your questions. Il 22 mar 2018 3:04 AM, "Tomas Vondra" <tomas.von...@2ndquadrant.com> ha scritto: > > On 03/21/2018 08:44 PM, Alessandro Aste wrote: > > Thanks for your reply Tomas. The query just got stuck for forever. I > > observed no CPU spikes, it is currently running and I see 89 of the CPU > > idle. I'm using a relative powerfull machine (120 GB ram, 56 CPU). > > > > That doesn't really answer the question, I'm afraid. I suppose "89 of > CPU" means that 89% idle in total, but 11% with 56 CPUs still means > about 6 cores 100% busy. But maybe you meant something else? > > Is there something else running on the machine? If you look at "top" are > the processes (the one you're connected to and the parallel workers) > doing something on the CPU? > > > > > Plain analyze as requested. : > > > > I don't see anything obviously broken with the query plan, and it's > difficult to compare with the other plans because they are quite different. > > But there's one part of the plan interesting: > > Limit (cost=253523.56..253523.57 rows=1 width=176) >-> Sort (cost=253523.56..253523.57 rows=1 width=176) > Sort Key: c_2.gii_circuitid, c_1.id > -> Nested Loop (cost=33190.89..253523.55 rows=1 width=176) >Join Filter: (c_1.id = c.id) >-> Nested Loop (cost=31724.87..31736.29 rows=1 ...) > ... >-> Gather (cost=1466.02..221787.23 rows=3 width=75) > Workers Planned: 5 > -> Hash Join (cost=466.02..220786.93 rows=1 ...) > ... > > That is, there's a Gather on the inner side of a Nested Loop. I wonder > if that might cause issues in case of under-estimate (in which case we'd > be restarting the Gather many times) ... > > > BTW one of the plans you sent earlier is incomplete, because it ends > like this: > > -> Nested Loop (cost=42469.41..42480.82 rows=1 width=85) (...) > Join Filter: (c.status = cst.id) > Time: 3016.688 ms (00:03.017) > > That is, it's missing the part below the join. > > > That being said, I'm not sure what's the issue here. Can you prepare a > self-contained test case that we might use to reproduce the issue? For > example by dumping the relevant part of the tables? > > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > QUERY PLAN Sort (cost=253514.80..253514.81 rows=1 width=176) Sort Key: c_2.gii_circuitid, c_1.id -> Hash Join (cost=33193.56..253514.79 rows=1 width=176) Hash Cond: (c_1.id = c.id) -> Gather (cost=1466.02..221787.23 rows=3 width=75) Workers Planned: 5 -> Hash Join (cost=466.02..220786.93 rows=1 width=75) Hash Cond: (c_1.id = vendor_gtt_pop.gtt_pop_id) -> Hash Join (cost=444.07..219779.19 rows=157724 width=63) Hash Cond: (c_1.pop_support_vendor_id = v.id) -> Merge Join (cost=5.02..217348.87 rows=157724 width=40) Merge Cond: (cl.circuit_id = c_1.id) -> Nested Loop (cost=0.86..171314.49 rows=157724 width=32) -> Parallel Index Only Scan using circuit_layout_idx on circuit_layout cl (cost=0.43..55430.93 rows=157724 width=8) Index Cond: (ordinal = 1) -> Index Scan using uniqid on segment sg (cost=0.43..0.73 rows=1 width=32) Index Cond: (id = cl.segment_i
Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on
(cost=0.42..41790.58 rows=909014 width=8) -> Hash (cost=325.69..325.69 rows=9069 width=27) -> Seq Scan on vendor v (cost=0.00..325.69 rows=9069 width=27) -> Hash (cost=21.91..21.91 rows=3 width=12) -> Seq Scan on vendor_gtt_pop (cost=0.00..21.91 rows=3 width=12) Filter: (vendor_id = 12346) (55 rows) On Wed, Mar 21, 2018 at 8:01 PM, Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > > > On 03/21/2018 05:09 PM, Alessandro Aste wrote: > > Hi there, we are using postgresql 10.3 and we're facing an issue with a > > query. The query (full query below) completes only when: > > > > 1 - LIMIT 10 is removed > > or > > 2 - show max_parallel_workers_per_gather is set to 0, so parallel > > processing is disabled. > > > > With max_parallel_workers_per_gather set to the default value (8) I'm > > not even able to get the query plan. > > > > Notes: > > > > * We're experiencing the issue in any server of ours but I've > > reproduced the issue in a fresh restored database with full > > vacuum/reindex of the tables. > > * We didn't touch any parameter concering the parallel processing, > > we're running the defaults: > > > > > > cmdstaging=# show max_parallel_workers_per_gather ; > > max_parallel_workers_per_gather > > - > > 8 > > (1 row) > > > > cmdstaging=# show max_worker_processes ; > > max_worker_processes > > -- > > 8 > > (1 row) > > > > cmdstaging=# show max_parallel_workers; > > max_parallel_workers > > -- > > 8 > > (1 row) > > > > > > > > > > The query completes only omitting the LIMIT clause or when I disable > > parallel processing: > > > > id | vendor_id | gaa | pop_name| pop_status | pop_location | > > pop_provider_id | pop_provider | pop_street | pop_city | pop > > _postal_code | pop_state | pop_country | pop_country_id > > +---+-+---++ > --+-+--+ > -+--+ > > -+---+-+ > > 684807 | 12346 | | GTT/POP/LON1T | Active | LON1T| > > 12288 | Telehouse UK | 14 Coriander Avenue | London | E14 > > 2AA | | GB |219 > > (1 row) > > > > Time: 4374.759 ms (00:04.375) > > cmdstaging=# show max_parallel_workers_per_gather ; > > max_parallel_workers_per_gather > > - > > 0 > > (1 row) > > > > Time: 0.097 ms > > > > > > Otherwise it just keep running for forever. > > > > When you say "running forever" is it actually using CPU, or does it get > stuck on something? > > > > > This is the full query: > > > > > > SELECT * FROM ( > > SELECT > > seg.circuit_id AS id, > > vendor_gtt_pop.vendor_id, > > CASE WHEN vendor_gtt_pop.push_to_gaa = 1 THEN 'Y' END as gaa, > > pop.gii_circuitid AS pop_name, > > cst.label AS pop_status, > > seg.a_company_name AS pop_location, > > seg.vendor_id AS pop_provider_id, > > seg.vendor_name AS pop_provider, > > cs.address1 AS pop_street, > > cs.city AS pop_city, > > cs.postal_code AS pop_postal_code, > > cs.state AS pop_state, > > csc.code AS pop_country, > > cs.country_id AS pop_country_id > > FROM ( > > SELECT c.gii_circuitid, max(so.id <http://so.id>) AS service_order_id > > FROM service_order so > > join circuit c on c.product_id=so.product_id > > join master_service_order mso on mso.id <http://mso.id>=so.master_serv > ice_order_id > > WHERE NOT (so.ordertype_id = 2 AND so.status <> 999) AND > > NOT (so.ordertype_id = 3 AND so.status <> 999) AND > > c.status >= 20 AND > > c.status not in (160,999) AND > > mso.client_id=11615 AND > > c.service_description=28 AND > > c.status!=160 > > GROUP BY c.gii_circuitid > > ) pop > > JOIN service_order so ON so.id <http://so.id> = pop.service_order_id > > left JOIN client_site cs on cs.id <http://cs.id>=so.a_site_id > > left JOIN country csc on csc.id <http://csc.id>=cs.country_id > > JOIN circuit c ON so.product_id=c.product_id > > JOIN circuit_status cst ON cst.id <http://
Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on
Usage: 102kB
-> Nested Loop (cost=31725.15..31727.53 rows=1 width=85)
(actual time=83.507..94.344 rows=882 loops=1)
-> Nested Loop (cost=31725.02..31727.38 rows=1
width=74) (actual time=83.496..93.575 rows=882 loops=1)
-> Nested Loop Left Join
(cost=31724.59..31726.89 rows=1 width=70) (actual time=83.484..90.885
rows=882 loops=1)
-> Nested Loop Left Join
(cost=31724.45..31726.73 rows=1 width=68) (actual time=83.475..90.067
rows=882 lo
ops=1)
-> Nested Loop
(cost=31724.02..31726.27 rows=1 width=30) (actual time=83.461..87.442
rows=882 loops=
1)
-> GroupAggregate
(cost=31723.60..31723.62 rows=1 width=26) (actual time=83.446..83.812 rows=8
82 loops=1)
Group Key:
c_2.gii_circuitid
-> Sort
(cost=31723.60..31723.60 rows=1 width=26) (actual time=83.441..83.515
rows=884 l
oops=1)
Sort Key:
c_2.gii_circuitid
Sort Method:
quicksort Memory: 94kB
-> Gather
(cost=1000.85..31723.59 rows=1 width=26) (actual time=2.159..81.342 rows
=884 loops=1)
Workers
Planned: 3
Workers
Launched: 2
-> Nested
Loop (cost=0.85..30723.49 rows=1 width=26) (actual time=8.509..75.
845 rows=295 loops=3)
->
Nested Loop (cost=0.42..30722.56 rows=2 width=30) (actual time=8.48
3..74.283 rows=295 loops=3)
-> Parallel Seq Scan on circuit c_2 (cost=0.00..30714.61 rows=3
width=26) (actual time=5.992..72.563 rows=316 loops=3)
Filter: ((status >= 20) AND (status <> ALL ('{160,999}'::int
eger[])) AND (status <> 160) AND (service_description = 28))
Rows Removed by Filter: 302689
-> Index Scan using so_pid_idx on service_order so_1 (cost=0.42.
.2.65 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=948)
Index Cond: (product_id = c_2.product_id)
Filter: (((ordertype_id <> 2) OR (status = 999)) AND ((order
type_id <> 3) OR (status = 999)))
Rows Removed by Filter: 0
->
Index Scan using master_service_order_id_key on master_service_order
mso (cost=0.42..0.46 rows=1 width=4) (actual time=0.005..0.005 rows=1
loops=884)
Index Cond: (id = so_1.master_service_order_id)
Filter: (client_id = 11615)
-> Index Scan using
service_order_id_key on service_order so (cost=0.42..2.64 rows=1 width=12)
(actual time=0.003..0.003 rows=1 loops=882)
Index Cond: (id = (max(
so_1.id)))
-> Index Scan using
client_site_pkey on client_site cs (cost=0.42..0.46 rows=1 width=46)
(actual tim
e=0.003..0.003 rows=1 loops=882)
Index Cond: (id = so.a_site_id)
-> Index Scan using country_pkey on
country csc (cost=0.14..0.16 rows=1 width=6) (actual time=0.001..0.001
rows=1 loops=882)
Index Cond: (id = cs.country_id)
-> Index Scan using circuit_product_id_idx on
circuit c (cost=0.42..0.49 rows=1 width=12) (actual time=0.003..0.
003 rows=1 loops=882)
Index Cond: (product_id = so.product_id)
-> Index Scan using cs_id_c on circuit_status cst
(cost=0.14..0.16 rows=1 width=19) (actual time=0.001..0.001 rows=1 l
oops=882)
Index Cond: (id = c.status)
Planning time: 3.889 ms
Execution time: 1122.357 ms
(68 rows)
Thank you very much for your help,
Kind regards,
Alessandro Aste.
RE: pgstattuple free_percent to high
Stephen, Rene - Thanks! Our experience teach us that above 20% of free space performance start to seriously deteriorate. I'm not sure if this is related to index or table fragmentation. We'll do our homework and we'll try to discover more. However we have identified a process potentially causing the free space spike high but it's not related to long running transactions modifying (update, insert) on those tables. What DEV is currently doing is create a sort of de-normalized cache table like this: * tablename is the table with the free space issue INSERT INTO cache_table SELECT *, table2., ...table.col FROM tablename JOIN table2 on ... ... .. JOIN table on So no updates/inserts/deletes to tablename are involved but that query may run for 15 (this is the AVG more or less) minutes holding an ACCESS SHARE lock (I guess). We are wondering why we have that spike if we are not modifying the relation and what we can suggest DEV from the query/db perspective to alleviate/fix the issue. Again, thanks so much. Thanks and kind regards Aste - alessandro.a...@gtt.net -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: 14 December 2017 16:13 To: Nicola Contu <nicola.co...@gmail.com> Cc: Rene Romero Benavides <rene.romer...@gmail.com>; pgsql-general@lists.postgresql.org; Alessandro Aste <alessandro.a...@gtt.net> Subject: Re: pgstattuple free_percent to high Greetings Nicola, * Nicola Contu (nicola.co...@gmail.com) wrote: > I think tuning the autovacuum settings may increase performances and > remove dead_tuples but as far as I know, the autovacuum runs a vacuum analyze. > The vacuum analyze won't touch the free_percent of the table. That's not entirely accurate. If all of the free space is at the *end* of the relation then autovacuum will attempt to lock the relation and truncate the table to give that free space back to the OS. On a table where all of the rows are regularly updated, eventually the "live" data should end up towards the front of the relation and the end of the relation will be all dead tuples, allowing the truncate to happen. If you have tuples at the end of the relation that aren't ever updated but they're "live" then we won't be able to truncate. The pg_freespacemap extension can be useful to see where the free space is in the relation. There are a few tools out there that aren't part of core PostgreSQL that you could consider using such as pg_repack and pg_squeeze. > So I'm trying to find a way to adjust the free percent for some tables > without doing a manually full vacuum. > We are now monitoring the free percent, so we may find the part of the > code that can increase that value, but was wondering if there is > anything on the postgres side to resolve this problem. Having some free space in the relation isn't a 'problem' and is a good thing because it means that new rows (from either INSERTs or UPDATEs) have a place to go that doesn't require extending the relation (which requires an additional lock as well as some additional work). As for how much free space is good to have and how much is too much depends on the specific workload. Thanks! Stephen
