Hi, On 2019-11-04 19:38:40 +0000, Scott Rankin wrote: > In the staging environment, we get this: > > Index Scan using "programPK" on public.program prog (cost=0.29..0.35 rows=1 > width=16) (actual time=0.002..0.003 rows=1 loops=21965) > Output: prog.id, prog.version, prog.active, prog.created_date, > prog.last_modified_date, prog.created_by, prog.last_modified_by, > prog.client_id, prog.scheme_id, prog.name, prog.legacy_group_id, > prog.custom_fields, prog.setup_complete, prog.setup_messages, > prog.legacy_program_type > Index Cond: (prog.id = per.program) > Buffers: shared hit=87860 > > In the production environment, we get this: > > Index Scan using "programPK" on public.program prog (cost=0.29..0.36 rows=1 > width=16) (actual time=0.017..4.251 rows=1 loops=21956) > Output: prog.id, prog.version, prog.active, prog.created_date, > prog.last_modified_date, prog.created_by, prog.last_modified_by, > prog.client_id, prog.scheme_id, prog.name, prog.legacy_group_id, > prog.custom_fields, prog.setup_complete, prog.setup_messages, > prog.legacy_program_type > Index Cond: (prog.id = per.program) > Buffers: shared hit=25437716 > > The tables in both environments are about the same size (18MB) and the > indexes are about the same size (360kb/410kb) – and the shared hits are > pretty much the same on the other nodes of the query between the two > environments.
It'd be worthwhile to look at the index stats using pgstatindex. Also, could you show the definition of those indexes? > This email message contains information that Motus, LLC considers > confidential and/or proprietary, or may later designate as confidential and > proprietary. It is intended only for use of the individual or entity named > above and should not be forwarded to any other persons or entities without > the express consent of Motus, LLC, nor should it be used for any purpose > other than in the course of any potential or actual business relationship > with Motus, LLC. If the reader of this message is not the intended recipient, > or the employee or agent responsible to deliver it to the intended recipient, > you are hereby notified that any dissemination, distribution, or copying of > this communication is strictly prohibited. If you have received this > communication in error, please notify sender immediately and destroy the > original message. > > Internal Revenue Service regulations require that certain types of written > advice include a disclaimer. To the extent the preceding message contains > advice relating to a Federal tax issue, unless expressly stated otherwise the > advice is not intended or written to be used, and it cannot be used by the > recipient or any other taxpayer, for the purpose of avoiding Federal tax > penalties, and was not written to support the promotion or marketing of any > transaction or matter discussed herein. GNGNGGRR. Greetings, Andres Freund
