2

For a personal data analysis project, I was trying to find the most frequent 3 products that were being sold together by doing a self join (twice).

As the query was taking over 15 mins and the laptop fans seemed to be going into overdrive, I decided to stop it. This was all done through pgAdmin.

Upon checking immediately afterwards, I noticed that the size of my free space on the C drive decreased by 7GB (from the 15GB that it had been before).

Tried doing a VACUUM and a VACCUM FULL without any improvement.

Did some digging with WinDirStat and TreeSize and as far as I can tell, the file that changed during the time that the query was running was the pagefile.sys one.

Did the virtual memory somehow increase automatically? I am absolutely stumped by this. Thoughts?

One further thing I have tried was to clear the above mentioned pagefile.sys on computer shutdown but with no change in its size.

FWIW, this is the output for VACUUM FULL VERBOSE ANALYZE:

INFO:  vacuuming "pg_catalog.pg_statistic"
INFO:  "pg_catalog.pg_statistic": found 0 removable, 441 nonremovable row versions in 28 pages
INFO:  vacuuming "pg_catalog.pg_type"
INFO:  "pg_catalog.pg_type": found 2 removable, 635 nonremovable row versions in 15 pages
INFO:  analyzing "pg_catalog.pg_type"
INFO:  "pg_type": scanned 15 of 15 pages, containing 633 live rows and 2 dead rows; 633 rows in sample, 633 estimated total rows
INFO:  vacuuming "pg_catalog.pg_foreign_table"
INFO:  "pg_catalog.pg_foreign_table": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_foreign_table"
INFO:  "pg_foreign_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_authid"
INFO:  "pg_catalog.pg_authid": found 0 removable, 15 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_authid"
INFO:  "pg_authid": scanned 1 of 1 pages, containing 15 live rows and 0 dead rows; 15 rows in sample, 15 estimated total rows
INFO:  vacuuming "pg_catalog.pg_statistic_ext_data"
INFO:  "pg_catalog.pg_statistic_ext_data": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_statistic_ext_data"
INFO:  "pg_statistic_ext_data": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_opclass"
INFO:  "pg_catalog.pg_opclass": found 0 removable, 177 nonremovable row versions in 3 pages
INFO:  analyzing "pg_catalog.pg_opclass"
INFO:  "pg_opclass": scanned 3 of 3 pages, containing 177 live rows and 0 dead rows; 177 rows in sample, 177 estimated total rows
INFO:  vacuuming "pg_catalog.pg_am"
INFO:  "pg_catalog.pg_am": found 0 removable, 7 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_am"
INFO:  "pg_am": scanned 1 of 1 pages, containing 7 live rows and 0 dead rows; 7 rows in sample, 7 estimated total rows
INFO:  vacuuming "pg_catalog.pg_amop"
INFO:  "pg_catalog.pg_amop": found 0 removable, 945 nonremovable row versions in 7 pages
INFO:  analyzing "pg_catalog.pg_amop"
INFO:  "pg_amop": scanned 7 of 7 pages, containing 945 live rows and 0 dead rows; 945 rows in sample, 945 estimated total rows
INFO:  vacuuming "pg_catalog.pg_amproc"
INFO:  "pg_catalog.pg_amproc": found 0 removable, 697 nonremovable row versions in 5 pages
INFO:  analyzing "pg_catalog.pg_amproc"
INFO:  "pg_amproc": scanned 5 of 5 pages, containing 697 live rows and 0 dead rows; 697 rows in sample, 697 estimated total rows
INFO:  vacuuming "pg_catalog.pg_language"
INFO:  "pg_catalog.pg_language": found 0 removable, 4 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_language"
INFO:  "pg_language": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
INFO:  vacuuming "pg_catalog.pg_largeobject_metadata"
INFO:  "pg_catalog.pg_largeobject_metadata": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_largeobject_metadata"
INFO:  "pg_largeobject_metadata": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_aggregate"
INFO:  "pg_catalog.pg_aggregate": found 0 removable, 157 nonremovable row versions in 2 pages
INFO:  analyzing "pg_catalog.pg_aggregate"
INFO:  "pg_aggregate": scanned 2 of 2 pages, containing 157 live rows and 0 dead rows; 157 rows in sample, 157 estimated total rows
INFO:  vacuuming "pg_catalog.pg_statistic_ext"
INFO:  "pg_catalog.pg_statistic_ext": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_statistic_ext"
INFO:  "pg_statistic_ext": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_opfamily"
INFO:  "pg_catalog.pg_opfamily": found 0 removable, 146 nonremovable row versions in 2 pages
INFO:  analyzing "pg_catalog.pg_opfamily"
INFO:  "pg_opfamily": scanned 2 of 2 pages, containing 146 live rows and 0 dead rows; 146 rows in sample, 146 estimated total rows
INFO:  vacuuming "information_schema.sql_features"
INFO:  "information_schema.sql_features": found 0 removable, 756 nonremovable row versions in 8 pages
INFO:  analyzing "information_schema.sql_features"
INFO:  "sql_features": scanned 8 of 8 pages, containing 756 live rows and 0 dead rows; 756 rows in sample, 756 estimated total rows
INFO:  vacuuming "pg_catalog.pg_trigger"
INFO:  "pg_catalog.pg_trigger": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_trigger"
INFO:  "pg_trigger": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_event_trigger"
INFO:  "pg_catalog.pg_event_trigger": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_event_trigger"
INFO:  "pg_event_trigger": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_description"
INFO:  "pg_catalog.pg_description": found 0 removable, 5170 nonremovable row versions in 45 pages
INFO:  analyzing "pg_catalog.pg_description"
INFO:  "pg_description": scanned 45 of 45 pages, containing 5170 live rows and 0 dead rows; 5170 rows in sample, 5170 estimated total rows
INFO:  vacuuming "pg_catalog.pg_cast"
INFO:  "pg_catalog.pg_cast": found 0 removable, 229 nonremovable row versions in 2 pages
INFO:  analyzing "pg_catalog.pg_cast"
INFO:  "pg_cast": scanned 2 of 2 pages, containing 229 live rows and 0 dead rows; 229 rows in sample, 229 estimated total rows
INFO:  vacuuming "pg_catalog.pg_enum"
INFO:  "pg_catalog.pg_enum": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_enum"
INFO:  "pg_enum": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_namespace"
INFO:  "pg_catalog.pg_namespace": found 0 removable, 4 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_namespace"
INFO:  "pg_namespace": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
INFO:  vacuuming "pg_catalog.pg_conversion"
INFO:  "pg_catalog.pg_conversion": found 0 removable, 128 nonremovable row versions in 2 pages
INFO:  analyzing "pg_catalog.pg_conversion"
INFO:  "pg_conversion": scanned 2 of 2 pages, containing 128 live rows and 0 dead rows; 128 rows in sample, 128 estimated total rows
INFO:  vacuuming "pg_catalog.pg_depend"
INFO:  "pg_catalog.pg_depend": found 86 removable, 1763 nonremovable row versions in 14 pages
INFO:  analyzing "pg_catalog.pg_depend"
INFO:  "pg_depend": scanned 13 of 13 pages, containing 1761 live rows and 2 dead rows; 1761 rows in sample, 1761 estimated total rows
INFO:  vacuuming "pg_catalog.pg_database"
INFO:  "pg_catalog.pg_database": found 0 removable, 21 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_database"
INFO:  "pg_database": scanned 1 of 1 pages, containing 21 live rows and 0 dead rows; 21 rows in sample, 21 estimated total rows
INFO:  vacuuming "pg_catalog.pg_tablespace"
INFO:  "pg_catalog.pg_tablespace": found 0 removable, 2 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_tablespace"
INFO:  "pg_tablespace": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  vacuuming "pg_catalog.pg_auth_members"
INFO:  "pg_catalog.pg_auth_members": found 0 removable, 3 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_auth_members"
INFO:  "pg_auth_members": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
INFO:  vacuuming "pg_catalog.pg_shdepend"
INFO:  "pg_catalog.pg_shdepend": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_shdepend"
INFO:  "pg_shdepend": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_shdescription"
INFO:  "pg_catalog.pg_shdescription": found 0 removable, 3 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_shdescription"
INFO:  "pg_shdescription": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
INFO:  vacuuming "pg_catalog.pg_ts_config"
INFO:  "pg_catalog.pg_ts_config": found 0 removable, 29 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_ts_config"
INFO:  "pg_ts_config": scanned 1 of 1 pages, containing 29 live rows and 0 dead rows; 29 rows in sample, 29 estimated total rows
INFO:  vacuuming "pg_catalog.pg_ts_config_map"
INFO:  "pg_catalog.pg_ts_config_map": found 0 removable, 551 nonremovable row versions in 3 pages
INFO:  analyzing "pg_catalog.pg_ts_config_map"
INFO:  "pg_ts_config_map": scanned 3 of 3 pages, containing 551 live rows and 0 dead rows; 551 rows in sample, 551 estimated total rows
INFO:  vacuuming "pg_catalog.pg_ts_dict"
INFO:  "pg_catalog.pg_ts_dict": found 0 removable, 29 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_ts_dict"
INFO:  "pg_ts_dict": scanned 1 of 1 pages, containing 29 live rows and 0 dead rows; 29 rows in sample, 29 estimated total rows
INFO:  vacuuming "pg_catalog.pg_ts_parser"
INFO:  "pg_catalog.pg_ts_parser": found 0 removable, 1 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_ts_parser"
INFO:  "pg_ts_parser": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  vacuuming "pg_catalog.pg_ts_template"
INFO:  "pg_catalog.pg_ts_template": found 0 removable, 5 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_ts_template"
INFO:  "pg_ts_template": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows
INFO:  vacuuming "pg_catalog.pg_extension"
INFO:  "pg_catalog.pg_extension": found 0 removable, 1 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_extension"
INFO:  "pg_extension": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  vacuuming "pg_catalog.pg_foreign_data_wrapper"
INFO:  "pg_catalog.pg_foreign_data_wrapper": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_foreign_data_wrapper"
INFO:  "pg_foreign_data_wrapper": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_foreign_server"
INFO:  "pg_catalog.pg_foreign_server": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_foreign_server"
INFO:  "pg_foreign_server": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_policy"
INFO:  "pg_catalog.pg_policy": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_policy"
INFO:  "pg_policy": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_replication_origin"
INFO:  "pg_catalog.pg_replication_origin": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_replication_origin"
INFO:  "pg_replication_origin": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_default_acl"
INFO:  "pg_catalog.pg_default_acl": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_default_acl"
INFO:  "pg_default_acl": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_init_privs"
INFO:  "pg_catalog.pg_init_privs": found 0 removable, 220 nonremovable row versions in 3 pages
INFO:  analyzing "pg_catalog.pg_init_privs"
INFO:  "pg_init_privs": scanned 3 of 3 pages, containing 220 live rows and 0 dead rows; 220 rows in sample, 220 estimated total rows
INFO:  vacuuming "pg_catalog.pg_seclabel"
INFO:  "pg_catalog.pg_seclabel": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_seclabel"
INFO:  "pg_seclabel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_shseclabel"
INFO:  "pg_catalog.pg_shseclabel": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_shseclabel"
INFO:  "pg_shseclabel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_collation"
INFO:  "pg_catalog.pg_collation": found 0 removable, 2245 nonremovable row versions in 41 pages
INFO:  analyzing "pg_catalog.pg_collation"
INFO:  "pg_collation": scanned 41 of 41 pages, containing 2245 live rows and 0 dead rows; 2245 rows in sample, 2245 estimated total rows
INFO:  vacuuming "pg_catalog.pg_parameter_acl"
INFO:  "pg_catalog.pg_parameter_acl": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_parameter_acl"
INFO:  "pg_parameter_acl": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_partitioned_table"
INFO:  "pg_catalog.pg_partitioned_table": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_partitioned_table"
INFO:  "pg_partitioned_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_range"
INFO:  "pg_catalog.pg_range": found 0 removable, 6 nonremovable row versions in 1 pages
INFO:  analyzing "pg_catalog.pg_range"
INFO:  "pg_range": scanned 1 of 1 pages, containing 6 live rows and 0 dead rows; 6 rows in sample, 6 estimated total rows
INFO:  vacuuming "pg_catalog.pg_transform"
INFO:  "pg_catalog.pg_transform": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_transform"
INFO:  "pg_transform": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_attrdef"
INFO:  "pg_catalog.pg_attrdef": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_attrdef"
INFO:  "pg_attrdef": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_constraint"
INFO:  "pg_catalog.pg_constraint": found 0 removable, 112 nonremovable row versions in 3 pages
INFO:  analyzing "pg_catalog.pg_constraint"
INFO:  "pg_constraint": scanned 3 of 3 pages, containing 112 live rows and 0 dead rows; 112 rows in sample, 112 estimated total rows
INFO:  vacuuming "pg_catalog.pg_inherits"
INFO:  "pg_catalog.pg_inherits": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_inherits"
INFO:  "pg_inherits": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_index"
INFO:  "pg_catalog.pg_index": found 1 removable, 173 nonremovable row versions in 4 pages
INFO:  analyzing "pg_catalog.pg_index"
INFO:  "pg_index": scanned 4 of 4 pages, containing 173 live rows and 0 dead rows; 173 rows in sample, 173 estimated total rows
INFO:  vacuuming "pg_catalog.pg_operator"
INFO:  "pg_catalog.pg_operator": found 0 removable, 799 nonremovable row versions in 14 pages
INFO:  analyzing "pg_catalog.pg_operator"
INFO:  "pg_operator": scanned 14 of 14 pages, containing 799 live rows and 0 dead rows; 799 rows in sample, 799 estimated total rows
INFO:  vacuuming "pg_catalog.pg_sequence"
INFO:  "pg_catalog.pg_sequence": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_sequence"
INFO:  "pg_sequence": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_user_mapping"
INFO:  "pg_catalog.pg_user_mapping": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_user_mapping"
INFO:  "pg_user_mapping": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_subscription"
INFO:  "pg_catalog.pg_subscription": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_subscription"
INFO:  "pg_subscription": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_attribute"
INFO:  "pg_catalog.pg_attribute": found 230 removable, 3345 nonremovable row versions in 77 pages
INFO:  analyzing "pg_catalog.pg_attribute"
INFO:  "pg_attribute": scanned 61 of 61 pages, containing 3313 live rows and 32 dead rows; 3313 rows in sample, 3313 estimated total rows
INFO:  vacuuming "pg_catalog.pg_proc"
INFO:  "pg_catalog.pg_proc": found 0 removable, 3297 nonremovable row versions in 96 pages
INFO:  analyzing "pg_catalog.pg_proc"
INFO:  "pg_proc": scanned 96 of 96 pages, containing 3297 live rows and 0 dead rows; 3297 rows in sample, 3297 estimated total rows
INFO:  vacuuming "pg_catalog.pg_publication_namespace"
INFO:  "pg_catalog.pg_publication_namespace": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_publication_namespace"
INFO:  "pg_publication_namespace": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_publication_rel"
INFO:  "pg_catalog.pg_publication_rel": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_publication_rel"
INFO:  "pg_publication_rel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_subscription_rel"
INFO:  "pg_catalog.pg_subscription_rel": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_subscription_rel"
INFO:  "pg_subscription_rel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "information_schema.sql_implementation_info"
INFO:  "information_schema.sql_implementation_info": found 0 removable, 12 nonremovable row versions in 1 pages
INFO:  analyzing "information_schema.sql_implementation_info"
INFO:  "sql_implementation_info": scanned 1 of 1 pages, containing 12 live rows and 0 dead rows; 12 rows in sample, 12 estimated total rows
INFO:  vacuuming "information_schema.sql_parts"
INFO:  "information_schema.sql_parts": found 0 removable, 11 nonremovable row versions in 1 pages
INFO:  analyzing "information_schema.sql_parts"
INFO:  "sql_parts": scanned 1 of 1 pages, containing 11 live rows and 0 dead rows; 11 rows in sample, 11 estimated total rows
INFO:  vacuuming "information_schema.sql_sizing"
INFO:  "information_schema.sql_sizing": found 0 removable, 23 nonremovable row versions in 1 pages
INFO:  analyzing "information_schema.sql_sizing"
INFO:  "sql_sizing": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO:  vacuuming "pg_catalog.pg_largeobject"
INFO:  "pg_catalog.pg_largeobject": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_largeobject"
INFO:  "pg_largeobject": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "public.customers"
INFO:  "public.customers": found 0 removable, 91 nonremovable row versions in 2 pages
INFO:  analyzing "public.customers"
INFO:  "customers": scanned 2 of 2 pages, containing 91 live rows and 0 dead rows; 91 rows in sample, 91 estimated total rows
INFO:  vacuuming "public.categories"
INFO:  "public.categories": found 0 removable, 8 nonremovable row versions in 1 pages
INFO:  analyzing "public.categories"
INFO:  "categories": scanned 1 of 1 pages, containing 8 live rows and 0 dead rows; 8 rows in sample, 8 estimated total rows
INFO:  vacuuming "public.employees"
INFO:  "public.employees": found 0 removable, 9 nonremovable row versions in 1 pages
INFO:  analyzing "public.employees"
INFO:  "employees": scanned 1 of 1 pages, containing 9 live rows and 0 dead rows; 9 rows in sample, 9 estimated total rows
INFO:  vacuuming "public.order_details"
INFO:  "public.order_details": found 0 removable, 2155 nonremovable row versions in 15 pages
INFO:  analyzing "public.order_details"
INFO:  "order_details": scanned 15 of 15 pages, containing 2155 live rows and 0 dead rows; 2155 rows in sample, 2155 estimated total rows
INFO:  vacuuming "public.orders"
INFO:  "public.orders": found 0 removable, 830 nonremovable row versions in 7 pages
INFO:  analyzing "public.orders"
INFO:  "orders": scanned 7 of 7 pages, containing 830 live rows and 0 dead rows; 830 rows in sample, 830 estimated total rows
INFO:  vacuuming "public.products"
INFO:  "public.products": found 0 removable, 77 nonremovable row versions in 1 pages
INFO:  analyzing "public.products"
INFO:  "products": scanned 1 of 1 pages, containing 77 live rows and 0 dead rows; 77 rows in sample, 77 estimated total rows
INFO:  vacuuming "public.shippers"
INFO:  "public.shippers": found 0 removable, 3 nonremovable row versions in 1 pages
INFO:  analyzing "public.shippers"
INFO:  "shippers": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
INFO:  vacuuming "public.monthly_date_dimension"
INFO:  "public.monthly_date_dimension": found 0 removable, 23 nonremovable row versions in 1 pages
INFO:  analyzing "public.monthly_date_dimension"
INFO:  "monthly_date_dimension": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO:  vacuuming "public.countries_only"
INFO:  "public.countries_only": found 0 removable, 69 nonremovable row versions in 1 pages
INFO:  analyzing "public.countries_only"
INFO:  "countries_only": scanned 1 of 1 pages, containing 69 live rows and 0 dead rows; 69 rows in sample, 69 estimated total rows
INFO:  vacuuming "pg_catalog.pg_rewrite"
INFO:  "pg_catalog.pg_rewrite": found 0 removable, 143 nonremovable row versions in 13 pages
INFO:  analyzing "pg_catalog.pg_rewrite"
INFO:  "pg_rewrite": scanned 13 of 13 pages, containing 143 live rows and 0 dead rows; 143 rows in sample, 143 estimated total rows
INFO:  vacuuming "pg_catalog.pg_db_role_setting"
INFO:  "pg_catalog.pg_db_role_setting": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_db_role_setting"
INFO:  "pg_db_role_setting": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "pg_catalog.pg_class"
INFO:  "pg_catalog.pg_class": found 23 removable, 442 nonremovable row versions in 13 pages
INFO:  analyzing "pg_catalog.pg_class"
INFO:  "pg_class": scanned 12 of 12 pages, containing 441 live rows and 2 dead rows; 441 rows in sample, 441 estimated total rows
INFO:  vacuuming "pg_catalog.pg_publication"
INFO:  "pg_catalog.pg_publication": found 0 removable, 0 nonremovable row versions in 0 pages
INFO:  analyzing "pg_catalog.pg_publication"
INFO:  "pg_publication": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  vacuuming "public.date_and_country"
INFO:  "public.date_and_country": found 0 removable, 1587 nonremovable row versions in 14 pages
INFO:  analyzing "public.date_and_country"
INFO:  "date_and_country": scanned 14 of 14 pages, containing 1587 live rows and 0 dead rows; 1587 rows in sample, 1587 estimated total rows
VACUUM

Query returned successfully in 22 secs 115 msec.
Improve this question
1
  • In a comment you said you had 4gb of physical RAM, I suggest you upgrade to at least 8gb so windows is less reliant on the pagefile.
    – cybernard
    Commented Jan 8 at 19:29

1 Answer 1

Reset to default
4

It's normal for the size of pagefile.sys to change if you have let it be set automatically. Its use indicates the PC did not have sufficient RAM to run the SQL query in memory, so it started to swap out sections to disk -- an exceedingly slow operation, even with SSD. Next time the PC is rebooted, pagefile.sys should shrink to its initial setting.

However, you may decide to set the size of that file yourself to optimize large queries and to reduce continual resizing of the file, which can cause fragmentation.

The size of pagefile.sys is set by the System Properties Control Panel.

  • Press WindowsR, type sysdm.cpl, press CtrlShiftEnter, and allow the System Properties CPL to run as Administrator.
  • On the Advanced tab, select Performance | Settings.
  • On the Advanced tab of the Performance Options dialog, select Virtual memory | Change.
  • Use the Virtual Memory dialog to set the size of pagefile.sys.

You can find various recommendations for setting that size. My inclination is to set a Custom size with initial size and maximum size set to the same value to prevent constant growth and shrinking on a HDD. The value suggested ranges from half the physical RAM (e.g., in the image below, from a 4 GB RAM machine, it's set to 2 GB), to twice that of RAM. Experiment with different amounts to see what is optimal for running SQL queries on your PC -- rebooting after changing page file size.

Setting virtual memory

Improve this answer
7
  • Thank you for your very helpful response! Follow-up question: checking the currently allocated value for my computer's virtual memory, it is already set to 9203 MB, with my machine having 4 GB of physical RAM. So it seems to (sort of) be aligned with your recommended values. Yet I suspect that it was smaller before my problem with the query, without reverting to its initial values after rebooting. Does that make sense?
    – M V
    Commented Jan 4 at 17:27
  • Yes, if you let the system resize it, it may trend towards the maximum. And setting it lower (e.g., 2 GB) might actually speed the query, since that reduces swapping to disk (though it might not, too -- try it and see). BTW, perform a full reboot; if Windows Fast Startup is on, it caches memory and does not truly reboot: windowscentral.com/how-disable-windows-10-fast-startup
    – DrMoishe Pippik
    Commented Jan 4 at 17:32
  • Will do! One more thing before I test out your ideas: in the VIrtual Memory window it also says that the "Space Available" for my C drive is close to 17 GB, but when I check by the usual Windows Explorer route, it only shows 7 GB as available space. Is that ... normal? Is that almost 10 GB difference related to pagefile.sys (which coincidentally is around 9 GB itself) ?
    – M V
    Commented Jan 4 at 17:42
  • oops... that is not enough headroom for Windows. Clear out some room! In addition to pagefile.sys, there's swapfile.sys and possibly hiberfil.sys.
    – DrMoishe Pippik
    Commented Jan 4 at 17:48
  • 1
    @DrMoishePippik Now that you mention it, I did indeed notice that WinSxs was getting a bit too large as well. Yet another super helpful link! Thank you once more!
    – M V
    Commented Jan 4 at 18:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .