Re: [PERFORM] Slow query with 3 table joins
> Hi Eskil - > > > The I believe the id-field you're referring to is the UNIT.UNIT_ID, I > could change this to a varchar, however that column is not used in the > query in question, so that wouldn't have any effect on the query's > performance. Sorry, I did not notice that the column "unit_id" existed in both "unit" and "field" tables. / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Slow query with 3 table joins
tis 2017-04-25 klockan 23:19 -0400 skrev Alessandro Ferrucci: > After about 40 inutes the slow query finally finished and the result > of the EXPLAIN plan can be found here: > > > https://explain.depesz.com/s/BX22 > > > Thanks, > Alessandro Ferrucci I'm not so familiar with the index implementetion in Postgres, but I don't think it is very efficient to index a text-field. It also loooks a bit strange that a id-field has the datatype "text" rather than integer or varchar. / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Create language plperlu Error
> Sir/Madam, > Plateform: RHEL6.5, Postgresql9.4.0. > > > create extension plperl; > > Create language plperl; > > > I have done following settings: > > Perl version 5.10 > vi /etc/ld.so.conf.d/libperl.conf > /usr/lib/5.10/multi-thread/i386.../CORE/libperl.so > ldconfig > > > ERROR: Can not load "/opt/Postgresql/9.4/lib/postgresql/plperl.so" > undefined symbol Perl_sv_2bool_flags > > > ERROR: could not load library > "/opt/PostgreSQL/9.4/lib/postgresql/plperl.so": > /opt/PostgreSQL/9.2/lib/postgresql/plperl.so: undefined symbol: > Perl_sv_2bool_flags > > How do I solve. > Kindly resolve it. > > Regards > Om Prakash Do you have the packet postgresql-plperl installed? / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance problems with 9.2.15
fre 2016-07-22 klockan 19:08 +1200 skrev Mark Kirkwood: > On 22/07/16 13:07, Johan Fredriksson wrote: > > And by the way, I have also tried to upgrade to Postgresql 9.4.8 (the > > latest version in postgresl.org's own repository) without improvment. > > > > Not sure what repo you are using, but 9.5.3 and 9.6 Beta are the > *actual* latest versions. Now I'm not sure they will actually help your > particular query, but are probably worth a try out! The one I found on https://www.postgresql.org/download/linux/redhat/ That page points out http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm as the latest. Perhaps the download-page need to be updated? / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance problems with 9.2.15
And by the way, I have also tried to upgrade to Postgresql 9.4.8 (the latest version in postgresl.org's own repository) without improvment. / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance problems with 9.2.15
I can add that setting enable_nestloop = 0 cuts the runtime for this query down to about 4 seconds. Disabling nested loops globaly does however impacts performance of a lot of other queries. / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance problems with 9.2.15
> > > The rowcount estimates from 9.2 seem greatly different from the 8.4 plan. > > > Did you remember to ANALYZE all the tables after migrating? Maybe there > > > were some table-specific statistics targets that you forgot to transfer > > > over? In any case, the 9.2 plan looks like garbage-in-garbage-out to > > > me :-( ... without estimates at least a little closer to reality, the > > > planner is unlikely to do anything very sane. > > > > > > (BTW, I wonder why you are moving only to 9.2 and not something more > > > recent.) > > > > You put me on the right track with your conclusion that the estimates > > were off the chart. The quick-and-dirty fix "DELETE FROM pg_statistic;" > > solved this problem. This database now have to build up sane estimates > > from scratch. > > Actually it took a VACUUM FULL; and DELETE FROM pg_statistic; followed > by ANALYZE on all tables to get it right. It worked last time, but this time it does not work. I have deleted all data in the table pg_statistic and run ANALYZE on all tables but the planner still make crappy optimizations. How can I adjust the estimates to make the planner work better? Last time it was in testing, this time it is in production, so urgent help is needed, please! This query now takes 90 seconds and it should not take more than 4-5 seconds. EXPLAIN ANALYZE VERBOSE SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 85) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC; QUERY PLAN --- --- --- Unique (cost=8907.68..8907.76 rows=1 width=336) (actual time=92075.721..92076.336 rows=176 loops=1) Output: main.id, main.name, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.gecos, m ain.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.creator, main.created, main.lastupdatedby, main. lastupdated, main.smimecertificate -> Sort (cost=8907.68..8907.69 rows=1 width=336) (actual time=92075.720..92075.748 rows=607 loops=1) Output: main.id, main.name, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.ge cos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.creator, main.created, main.lastupdatedby, main.lastupdated, main.smimecertificate Sort Key: main.name, main.id, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main. gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.creator, main.created, main.lastupdatedb y, main.lastupdated, main.smimecertificate Sort Method: quicksort Memory: 243kB -> Nested Loop (cost=20.37..8907.67 rows=1 width=336) (actual
Re: [PERFORM] Performance problems with 9.2.15
> > The rowcount estimates from 9.2 seem greatly different from the 8.4 plan. > > Did you remember to ANALYZE all the tables after migrating? Maybe there > > were some table-specific statistics targets that you forgot to transfer > > over? In any case, the 9.2 plan looks like garbage-in-garbage-out to > > me :-( ... without estimates at least a little closer to reality, the > > planner is unlikely to do anything very sane. > > > > (BTW, I wonder why you are moving only to 9.2 and not something more > > recent.) > > You put me on the right track with your conclusion that the estimates > were off the chart. The quick-and-dirty fix "DELETE FROM pg_statistic;" > solved this problem. This database now have to build up sane estimates > from scratch. Actually it took a VACUUM FULL; and DELETE FROM pg_statistic; followed by ANALYZE on all tables to get it right. Can someone please explain to me the difference between these two query plans: The bad one: Unique (cost=6037.10..6037.18 rows=1 width=434) (actual time=255608.588..255646.828 rows=572 loops=1) -> Sort (cost=6037.10..6037.11 rows=1 width=434) (actual time=255608.583..255611.632 rows=33209 loops=1) Sort Method: quicksort Memory: 13143kB -> Nested Loop (cost=42.51..6037.09 rows=1 width=434) (actual time=152.818..254886.674 rows=33209 loops=1) Join Filter: (cachedgroupmembers_4.groupid = acl_3.principalid) Rows Removed by Join Filter: 495425041 -> Bitmap Heap Scan on public.acl acl_3 (cost=30.07..144.35 rows=497 width=4) (actual time=0.284..8.184 rows=525 loops=1) Recheck Cond: acl_3.rightname)::text = 'OwnTicket'::text) AND ((acl_3.principaltype)::text = 'Group'::text)) OR (((acl_3.rightname)::text = 'SuperUser'::text) AND ((acl_3.principaltype): :text = 'Group'::text))) Filter: (((acl_3.objecttype)::text = 'RT::Queue'::text) OR (((acl_3.objecttype)::text = 'RT::System'::text) AND (acl_3.objectid = 1))) -> BitmapOr (cost=30.07..30.07 rows=531 width=0) (actual time=0.249..0.249 rows=0 loops=1) -> Bitmap Index Scan on acl1 (cost=0.00..25.46 rows=521 width=0) (actual time=0.233..0.233 rows=521 loops=1) Index Cond: (((acl_3.rightname)::text = 'OwnTicket'::text) AND ((acl_3.principaltype)::text = 'Group'::text)) -> Bitmap Index Scan on acl1 (cost=0.00..4.36 rows=11 width=0) (actual time=0.016..0.016 rows=4 loops=1) Index Cond: (((acl_3.rightname)::text = 'SuperUser'::text) AND ((acl_3.principaltype)::text = 'Group'::text)) -> Materialize (cost=12.44..5870.39 rows=3 width=438) (actual time=0.004..176.296 rows=943730 loops=525) -> Nested Loop (cost=12.44..5870.37 rows=3 width=438) (actual time=0.351..1028.683 rows=943730 loops=1) -> Nested Loop (cost=12.44..5601.49 rows=2 width=442) (actual time=0.326..15.591 rows=675 loops=1) -> Nested Loop (cost=12.44..5502.26 rows=27 width=8) (actual time=0.303..9.744 rows=675 loops=1) Output: principals_1.id, cachedgroupmembers_2.memberid -> Bitmap Heap Scan on public.cachedgroupmembers cachedgroupmembers_2 (cost=12.44..1659.12 rows=446 width=4) (actual time=0.267..1.266 rows=676 loops=1) Recheck Cond: (cachedgroupmembers_2.groupid = 4) Filter: (cachedgroupmembers_2.disabled = 0) -> Bitmap Index Scan on cachedgroupmembers2 (cost=0.00..12.33 rows=446 width=0) (actual time=0.171..0.171 rows=676 loops=1) Index Cond: (cachedgroupmembers_2.groupid = 4) -> Index Scan using principals_pkey on public.principals principals_1 (cost=0.00..8.61 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=676) Output: principals_1.id Index Cond: (principals_1.id = cachedgroupmembers_2.memberid) Filter: ((principals_1.id <> 1) AND (principals_1.disabled = 0) AND ((principals_1.principaltype)::text = 'User'::text)) Rows Removed by Filter: 0 -> Index Scan using users_pkey on public.users main (cost=0.00..3.67 rows=1 width=434) (actual time=0.007..0.008 rows=1 loops=675) Index Cond: (main.id = principals_1.id) -> Index Scan using cachedgroupmembers1 on public.cachedgroupmembers cachedgroupmembers_4 (cost=0.00..133.79 rows=65 width=8) (actual time=0.012..1.199 rows=1398 loops=675)
Re: [PERFORM] Performance problems with 9.2.15
> > I am just about to upgrade from PostgreSQL 8.4.20 to 9.2.15, but I'v run > > into some huge performance issues. > > The rowcount estimates from 9.2 seem greatly different from the 8.4 plan. > Did you remember to ANALYZE all the tables after migrating? Maybe there > were some table-specific statistics targets that you forgot to transfer > over? In any case, the 9.2 plan looks like garbage-in-garbage-out to > me :-( ... without estimates at least a little closer to reality, the > planner is unlikely to do anything very sane. > > (BTW, I wonder why you are moving only to 9.2 and not something more > recent.) You put me on the right track with your conclusion that the estimates were off the chart. The quick-and-dirty fix "DELETE FROM pg_statistic;" solved this problem. This database now have to build up sane estimates from scratch. / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance problems with 9.2.15
The rowcount estimates from 9.2 seem greatly different from the 8.4 plan. Did you remember to ANALYZE all the tables after migrating? Maybe there were some table-specific statistics targets that you forgot to transfer over? No, I did not. Honestly I though everything would be transfered with a dump/restore procedure. Unfortunatly running ANALYZE VERBOSE on all involved tables did not really improve anything. > In any case, the 9.2 plan looks like garbage-in-garbage-out to me :-( ... without estimates at least a little closer to reality, the planner is unlikely to do anything very sane. (BTW, I wonder why you are moving only to 9.2 and not something more recent.) Well, 9.2.15 is what comes bundled with RHEL 7, so I decided to go with that to avoid dependency issues. But I could install a more fresh version from scratch if that would solve my problem. / Eskil -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Performance problems with 9.2.15
Hello! I am just about to upgrade from PostgreSQL 8.4.20 to 9.2.15, but I'v run into some huge performance issues. Both databases are configured the same way (shared_buffers = 2GB, temp_buffers = 32MB). I have increased work_mem on the 9.2 from 4MB to 64MB, but to no avail. Now, the query on 8.4: rt4=# EXPLAIN ANALYZE VERBOSE SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue') OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC; QUERY PLAN --- --- --- Unique (cost=19822.31..19843.46 rows=235 width=1084) (actual time=6684.054..7118.015 rows=571 loops=1) Output: main.id, main.name, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.emailenc oding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.address1, ma in.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated -> Sort (cost=19822.31..19822.90 rows=235 width=1084) (actual time=6684.052..7085.835 rows=33310 loops=1) Output: main.id, main.name, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main.em ailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.addres s1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated Sort Key: main.name, main.id, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, main. emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main.addr ess1, main.address2, main.city, main.state, main.zip, main.country, main.timezone, main.pgpkey, main.creator, main.created, main.lastupdatedby, main.lastupdated Sort Method: external merge Disk: 7408kB -> Hash Join (cost=19659.66..19813.05 rows=235 width=1084) (actual time=3362.897..4080.600 rows=33310 loops=1) Output: main.id, main.name, main.password, main.authtoken, main.comments, main.signature, main.emailaddress, main.freeformcontactinfo, main.organization, main.realname, main.nickname, main.lang, m ain.emailencoding, main.webencoding, main.externalcontactinfoid, main.contactinfosystem, main.externalauthid, main.authsystem, main.gecos, main.homephone, main.workphone, main.mobilephone, main.pagerphone, main. address1, main.address2, main.city, main.state, main.zip,
