[no subject]
Hi,
I have one query like below :
SELECT
m.iMemberId "memberId",
m.cFirstName "firstName",
m.cLastName "lastName",
m.cFirstName || ' ' ||
m.cLastName "fullName",
m.cPlayerStateId "stateId",
DECODE(m.cBirthdateVerify, 1,
'Yes', 'No') "birthdateVerify",
TO_CHAR(m.dBirthDate,
'MM/DD/') "dateOfBirth",
p.cPosition "position",
p.cJerseyNumber "number",
DECODE(daps.status, 2, 'PT',
1, 'FT', NULL) "daps",
op.cCitizenship "citizenship",
op.cNotes "notes",
NVL(op.cCountryOfBirth,
op.cCountryOfBirthOther) "countryOfBirth"
FROM sam_gameroster r
INNER JOIN sam_guestParticipant p ON
p.iMemberId = r.iMemberId AND p.iContainerId = r.iContainerId
INNER JOIN sam_member m ON m.iMemberId
= p.iMemberId
INNER JOIN sam_container c ON
c.iContainerId = r.iContainerId
LEFT JOIN sam_container lc ON
c.iContainerLinkId = lc.iContainerId
LEFT JOIN sam_participant op ON
op.iassignContainerId = NVL(c.iContainerLinkId, c.iContainerId) AND
op.imemberID = m.imemberId
LEFT JOIN (
SELECT pp.iMemberId,
MAX(CASE WHEN
pp.cDpFtStatus = 'PT' THEN 2
WHEN
pp.cDpFtStatus = 'FT' THEN 1
ELSE 0 END) status
FROM sam_participant pp
WHERE pp.igroupid =
getGroupId() GROUP BY pp.iMemberId
) daps ON daps.iMemberId = r.iMemberId
LEFT JOIN sam_playersuspension ps ON
ps.iMemberId = m.iMemberId AND ps.iEventId = r.iEventId
WHERE r.iEventId = '7571049' AND
r.iContainerId = '15257396' AND ((ps.iIsAutoCreated = 1 AND
ps.iIsTombstone = 1) OR ps.iPlayerSuspensionId IS NULL)
ORDER BY LOWER(m.cLastName), LOWER(m.cFirstName)
And the execution of above query is
QUERY PLAN
Sort (cost=783789.11..783789.11 rows=1 width=377) (actual
time=12410.619..12410.619 rows=0 loops=1)
Sort Key: (lower((m.clastname)::text)), (lower((m.cfirstname)::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=525065
-> Merge Right Join (cost=781822.64..783789.10 rows=1 width=377)
(actual time=12410.609..12410.609 rows=0 loops=1)
Merge Cond: (pp.imemberid = r.imemberid)
Buffers: shared hit=525065
-> GroupAggregate (cost=781820.08..783074.57 rows=55308
width=11) (actual time=12410.251..12410.251 rows=1 loops=1)
Group Key: pp.imemberid
Buffers: shared hit=524884
-> Sort (cost=781820.08..781960.36 rows=56113
width=10) (actual time=12410.234..12410.234 rows=3 loops=1)
Sort Key: pp.imemberid
Sort Method: quicksort Memory: 207217kB
Buffers: shared hit=524884
-> Seq Scan on sam_participant pp
(cost=0.00..777393.87 rows=56113 width=10) (actual
time=0.284..10871.913 rows=2335154 loops=1)
Filter: ((igroupid)::integer =
(current_setting('env.groupid'::text))::integer)
Rows Removed by Filter: 8887508
Buffers: shared hit=524884
-> Materialize (cost=2.56..23.14 rows=1 width=184) (actual
time=0.354..0.354 rows=0 loops=1)
Buffers: shared hit=181
-> Nested Loop Left Join (cost=2.56..23.14 rows=1
width=184) (actual time=0.352..0.353 rows=0 loops=1)
Join Filter: (ps.ieventid = r.ieventid)
Filter: (((ps.iisautocreated = '1'::numeric) AND
(ps.iistombstone = '1'::numeric)) OR (ps.iplayersuspensionid IS NULL))
Buffers: shared hit=181
-> Nested Loop Left Join (cost=2.28..22.82
rows=1 width=190) (actual time=0.351..0.351 rows=0 loops=1)
Join Filter: (op.iassigncontainerid =
nvl(c.icontainerlinkid, c.icontainerid))
Buffers: shared hit=181
Re:
Hi,
On Fri, Jul 2, 2021, 5:24 AM Atul Kumar wrote:
> Hi,
>
> I have one query like below :
>
>
> SELECT
> m.iMemberId "memberId",
> m.cFirstName "firstName",
> m.cLastName "lastName",
> m.cFirstName || ' ' ||
> m.cLastName "fullName",
> m.cPlayerStateId "stateId",
> DECODE(m.cBirthdateVerify, 1,
> 'Yes', 'No') "birthdateVerify",
> TO_CHAR(m.dBirthDate,
> 'MM/DD/') "dateOfBirth",
> p.cPosition "position",
> p.cJerseyNumber "number",
> DECODE(daps.status, 2, 'PT',
> 1, 'FT', NULL) "daps",
> op.cCitizenship "citizenship",
> op.cNotes "notes",
> NVL(op.cCountryOfBirth,
> op.cCountryOfBirthOther) "countryOfBirth"
> FROM sam_gameroster r
> INNER JOIN sam_guestParticipant p ON
> p.iMemberId = r.iMemberId AND p.iContainerId = r.iContainerId
> INNER JOIN sam_member m ON m.iMemberId
> = p.iMemberId
> INNER JOIN sam_container c ON
> c.iContainerId = r.iContainerId
> LEFT JOIN sam_container lc ON
> c.iContainerLinkId = lc.iContainerId
> LEFT JOIN sam_participant op ON
> op.iassignContainerId = NVL(c.iContainerLinkId, c.iContainerId) AND
> op.imemberID = m.imemberId
> LEFT JOIN (
> SELECT pp.iMemberId,
> MAX(CASE WHEN
> pp.cDpFtStatus = 'PT' THEN 2
> WHEN
> pp.cDpFtStatus = 'FT' THEN 1
> ELSE 0 END) status
> FROM sam_participant pp
> WHERE pp.igroupid =
> getGroupId() GROUP BY pp.iMemberId
> ) daps ON daps.iMemberId = r.iMemberId
> LEFT JOIN sam_playersuspension ps ON
> ps.iMemberId = m.iMemberId AND ps.iEventId = r.iEventId
> WHERE r.iEventId = '7571049' AND
> r.iContainerId = '15257396' AND ((ps.iIsAutoCreated = 1 AND
> ps.iIsTombstone = 1) OR ps.iPlayerSuspensionId IS NULL)
> ORDER BY LOWER(m.cLastName),
> LOWER(m.cFirstName)
>
>
>
>
>
> And the execution of above query is
>
>
>
>
> QUERY PLAN
>
>
> Sort (cost=783789.11..783789.11 rows=1 width=377) (actual
> time=12410.619..12410.619 rows=0 loops=1)
>Sort Key: (lower((m.clastname)::text)), (lower((m.cfirstname)::text))
>Sort Method: quicksort Memory: 25kB
>Buffers: shared hit=525065
>-> Merge Right Join (cost=781822.64..783789.10 rows=1 width=377)
> (actual time=12410.609..12410.609 rows=0 loops=1)
> Merge Cond: (pp.imemberid = r.imemberid)
> Buffers: shared hit=525065
> -> GroupAggregate (cost=781820.08..783074.57 rows=55308
> width=11) (actual time=12410.251..12410.251 rows=1 loops=1)
>Group Key: pp.imemberid
>Buffers: shared hit=524884
>-> Sort (cost=781820.08..781960.36 rows=56113
> width=10) (actual time=12410.234..12410.234 rows=3 loops=1)
> Sort Key: pp.imemberid
> Sort Method: quicksort Memory: 207217kB
> Buffers: shared hit=524884
> -> Seq Scan on sam_participant pp
> (cost=0.00..777393.87 rows=56113 width=10) (actual
> time=0.284..10871.913 rows=2335154 loops=1)
>Filter: ((igroupid)::integer =
> (current_setting('env.groupid'::text))::integer)
>Rows Removed by Filter: 8887508
>Buffers: shared hit=524884
> -> Materialize (cost=2.56..23.14 rows=1 width=184) (actual
> time=0.354..0.354 rows=0 loops=1)
>Buffers: shared hit=181
>-> Nested Loop Left Join (cost=2.56..23.14 rows=1
> width=184) (actual time=0.352..0.353 rows=0 loops=1)
> Join Filter: (ps.ieventid = r.ieventid)
> Filter: (((ps.iisautocreated = '1'::numeric) AND
> (ps.iistombstone = '1'::numeric)) OR (ps.iplayersuspensionid IS NULL))
> Buffers: shared hit=181
> -> Nested Loop Left Join (cost=
Re: Damaged (during upgrade?) table, how to repair?
On 7/1/21 9:24 PM, W.P. wrote: W dniu 01.07.2021 o 22:27, Adrian Klaver pisze: On 7/1/21 12:56 PM, W.P. wrote: W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS upgrade). One table is now damaged, trying to dump it results in server restart, message is "invalid record length maximum is yyy" (from memory). How can I (?) repair this table? (for recent data this works OK). If you have a backup, take that. If not, hire an expert in data recovery. Yours, Laurenz Albe I have something similar to backup: old database, but it is 9.5, how can I install 9.5 binaries / libs / config on Fedora 30 i386 (with PG11 installed) side-by-side? Is the old database on another machine where it can be started and then the data dumped? It is on another disc, with OS that has problems (F24 does not boot beyond single user mode, Network Manager doesn't start) after removing power / battery while in "suspend to RAM". Thats why I think about setting 9.5 on my current machine (F30/PG11 cluster) and then do a dump, only I need to know how to do it (binaries / libs / default cfg + data copy). So you have backup of the failed machine's disk stored somewhere else? Otherwise how are you going to get the 9.5 instance to the new machine? Laurent -- Adrian Klaver adrian.kla...@aklaver.com
[no subject]
Igor Korot writes:
> On Fri, Jul 2, 2021, 5:24 AM Atul Kumar wrote:
>> -> Seq Scan on sam_participant pp (cost=0.00..777393.87 rows=56113
>> width=10) (actual time=0.277..10869.750 rows=2335154 loops=1)
>> Filter: ((igroupid)::integer =
>> (current_setting('env.groupid'::text))::integer)
>> I have already an index on the column igroupid of table
>> sam_participant, but still it is doig seq scan, which is time
>> consuming or is their something else is fishy.
Where is that cast to integer coming from? That's likely causing
the WHERE clause to not match your index. What's the actual type
of the igroupid column?
regards, tom lane
Re: Damaged (during upgrade?) table, how to repair?
W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: On 7/1/21 9:24 PM, W.P. wrote: W dniu 01.07.2021 o 22:27, Adrian Klaver pisze: On 7/1/21 12:56 PM, W.P. wrote: W dniu 01.07.2021 o 16:19, Laurenz Albe pisze: On Thu, 2021-07-01 at 10:56 +0200, W.P. wrote: I have a PG 11.12 (was 9.5 or 9.5) upgraded from 9.x (during OS upgrade). One table is now damaged, trying to dump it results in server restart, message is "invalid record length maximum is yyy" (from memory). How can I (?) repair this table? (for recent data this works OK). If you have a backup, take that. If not, hire an expert in data recovery. Yours, Laurenz Albe I have something similar to backup: old database, but it is 9.5, how can I install 9.5 binaries / libs / config on Fedora 30 i386 (with PG11 installed) side-by-side? Is the old database on another machine where it can be started and then the data dumped? It is on another disc, with OS that has problems (F24 does not boot beyond single user mode, Network Manager doesn't start) after removing power / battery while in "suspend to RAM". Thats why I think about setting 9.5 on my current machine (F30/PG11 cluster) and then do a dump, only I need to know how to do it (binaries / libs / default cfg + data copy). So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". Otherwise how are you going to get the 9.5 instance to the new machine? In modern Postgres I've seen there is possibility to have several (different versions) "clusters" on same machine. My question (for now) is how to do it? (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, share, postgresql-9.6 is empty). Laurent
Re: Damaged (during upgrade?) table, how to repair?
On 7/2/21 10:18 AM, W.P. wrote: W dniu 02.07.2021 o 17:16, Adrian Klaver pisze: So you have backup of the failed machine's disk stored somewhere else? No, I have disc from this machine, looks not damaged (random files). Only problem that OS does not boot beyond "emergency mode". I would say your second sentence contradicts your first. In any case, we got to this point as the upgrade(more information on how that was done would be nice) from the 9.5 instance to 11.12 failed. Given that there is good chance that was due to corruption of files in the Postgres cluster on the above disc, it is very probable that going back to that disk will repeat the problem. Still see more below. Otherwise how are you going to get the 9.5 instance to the new machine? In modern Postgres I've seen there is possibility to have several (different versions) "clusters" on same machine. My question (for now) is how to do it? I'm going to say the easiest way to do this on the new machine would be to build 9.5 from source: https://www.postgresql.org/docs/9.5/installation.html https://www.postgresql.org/ftp/source/v9.5.25/ This will require installing development packages on your new machine. (I can see PG10 somewhere in /usr/pgsql/postgresql-10/ - bin, lib, share, postgresql-9.6 is empty). Where do you see the above, old machine or new machine? Laurent -- Adrian Klaver adrian.kla...@aklaver.com
