{"action":"created","issue":{"url":"https://api.github.com/repos/CartoDB/cartodb-postgresql/issues/65","labels_url":"https://api.github.com/repos/CartoDB/cartodb-postgresql/issues/65/labels{/name}","comments_url":"https://api.github.com/repos/CartoDB/cartodb-postgresql/issues/65/comments","events_url":"https://api.github.com/repos/CartoDB/cartodb-postgresql/issues/65/events","html_url":"https://github.com/CartoDB/cartodb-postgresql/issues/65","id":53035296,"number":65,"title":"Slow quota check","user":{"login":"juanignaciosl","id":932968,"avatar_url":"https://avatars.githubusercontent.com/u/932968?v=3","gravatar_id":"","url":"https://api.github.com/users/juanignaciosl","html_url":"https://github.com/juanignaciosl","followers_url":"https://api.github.com/users/juanignaciosl/followers","following_url":"https://api.github.com/users/juanignaciosl/following{/other_user}","gists_url":"https://api.github.com/users/juanignaciosl/gists{/gist_id}","starred_url":"https://api.github.com/users/juanignaciosl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/juanignaciosl/subscriptions","organizations_url":"https://api.github.com/users/juanignaciosl/orgs","repos_url":"https://api.github.com/users/juanignaciosl/repos","events_url":"https://api.github.com/users/juanignaciosl/events{/privacy}","received_events_url":"https://api.github.com/users/juanignaciosl/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"Kartones","id":2085449,"avatar_url":"https://avatars.githubusercontent.com/u/2085449?v=3","gravatar_id":"","url":"https://api.github.com/users/Kartones","html_url":"https://github.com/Kartones","followers_url":"https://api.github.com/users/Kartones/followers","following_url":"https://api.github.com/users/Kartones/following{/other_user}","gists_url":"https://api.github.com/users/Kartones/gists{/gist_id}","starred_url":"https://api.github.com/users/Kartones/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Kartones/subscriptions","organizations_url":"https://api.github.com/users/Kartones/orgs","repos_url":"https://api.github.com/users/Kartones/repos","events_url":"https://api.github.com/users/Kartones/events{/privacy}","received_events_url":"https://api.github.com/users/Kartones/received_events","type":"User","site_admin":false},"milestone":null,"comments":1,"created_at":"2014-12-29T11:31:57Z","updated_at":"2015-01-28T08:44:52Z","closed_at":null,"body":"Finding out why CartoDB/cartodb#1573 threw timeout errors, we found out the following issue at `CDB_UserDataSize`:\r\n\r\n```sql\r\n# explain analyze SELECT COALESCE(INT8(SUM(pg_total_relation_size('juanignaciosl.' || table_name))), 0) FROM information_schema.tables                                                                                                                                                                                 WHERE table_catalog = current_database() AND table_schema = 'juanignaciosl'                                                                                                                                                                                                                                                                                                 AND table_name != 'spatial_ref_sys'                                                                                                                                                                                                                                                                                                                                         AND table_name != 'cdb_tablemetadata'                                                                                                                                                                                                                                                                                                                                       AND table_type = 'BASE TABLE'                                                                                                                                                                                                                                                                                                                                               -- exclude raster overview tables                                                                                                                                                                                                                                                                                                                                           AND table_name NOT IN (                                                                                                                                                                                                                                                                                                                                                       SELECT o_table_name FROM raster_overviews                                                                                                                                                                                                                                                                                                                                   WHERE o_table_schema = 'juanignaciosl' AND o_table_catalog = current_database()                                                                                                                                                                                                                                                                                           )                                                                                                                                                                                                                                                                                                                                                                           -- filter to raster \"main\" tables                                                                                                                                                                                                                                                                                                                                           AND table_name IN (                                                                                                                                                                                                                                                                                                                                                           SELECT r_table_name FROM raster_overviews                                                                                                                                                                                                                                                                                                                                   WHERE r_table_name = table_name                                                                                                                                                                                                                                                                                                                                             AND o_table_schema = 'juanignaciosl' AND o_table_catalog = current_database()                                                                                                                                                                                                                                                                                             );\r\n```\r\n\r\n```\r\n Aggregate  (cost=180666.03..180666.03 rows=1 width=64) (actual time=890.094..890.094 rows=1 loops=1)\r\n   ->  Result  (cost=6730.70..180666.03 rows=1 width=64) (actual time=890.087..890.087 rows=0 loops=1)\r\n         One-Time Filter: (((current_database())::information_schema.sql_identifier)::name = current_database())\r\n         ->  Nested Loop Left Join  (cost=6730.70..180666.03 rows=1 width=64) (actual time=890.079..890.079 rows=0 loops=1)\r\n               ->  Nested Loop  (cost=6730.58..180661.16 rows=1 width=68) (actual time=890.078..890.078 rows=0 loops=1)\r\n                     ->  Seq Scan on pg_namespace nc  (cost=0.00..3.24 rows=1 width=4) (actual time=0.033..0.048 rows=1 loops=1)\r\n                           Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text = 'juanignaciosl'::text))\r\n                           Rows Removed by Filter: 53\r\n                     ->  Bitmap Heap Scan on pg_class c  (cost=6730.58..180657.92 rows=1 width=73) (actual time=890.023..890.023 rows=0 loops=1)\r\n                           Recheck Cond: (relnamespace = nc.oid)\r\n                           Filter: ((relkind = ANY ('{r,v,f}'::\"char\"[])) AND (((relname)::information_schema.sql_identifier)::text <> 'spatial_ref_sys'::text) AND (((relname)::information_schema.sql_identifier)::text <> 'cdb_tablemetadata'::text) AND (NOT (hashed SubPlan 1)) AND (((CASE WHEN (nc.oid = pg_my_temp_schema()) THEN 'LOCAL TEMPORARY'::text WHEN (relkind = 'r'::\"char\") THEN 'BASE TABLE'::text WHEN (relkind = 'v'::\"char\") THEN 'VIEW'::text WHEN (relkind = 'f'::\"char\") THEN 'FOREIGN TABLE'::text ELSE NULL::text END)::information_schema.character_data)::text = 'BASE TABLE'::text) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)) AND (SubPlan 2))\r\n                           Rows Removed by Filter: 741\r\n                           ->  Bitmap Index Scan on pg_class_relname_nsp_index  (cost=0.00..6319.72 rows=806 width=0) (actual time=3.426..3.426 rows=943 loops=1)\r\n                                 Index Cond: (relnamespace = nc.oid)\r\n                           SubPlan 1\r\n                             ->  Result  (cost=0.25..410.86 rows=1 width=64) (actual time=6.298..6.298 rows=0 loops=1)\r\n                                   One-Time Filter: (current_database() = current_database())\r\n                                   ->  Nested Loop  (cost=0.25..410.86 rows=1 width=64) (actual time=6.294..6.294 rows=0 loops=1)\r\n                                         ->  Nested Loop  (cost=0.17..410.75 rows=1 width=68) (actual time=6.292..6.292 rows=0 loops=1)\r\n                                               ->  Nested Loop  (cost=0.08..385.16 rows=1 width=72) (actual time=6.292..6.292 rows=0 loops=1)\r\n                                                     Join Filter: (n.oid = c_1.relnamespace)\r\n                                                     ->  Nested Loop  (cost=0.00..369.50 rows=2 width=12) (actual time=6.290..6.290 rows=0 loops=1)\r\n                                                           Join Filter: (n.oid = s.connamespace)\r\n                                                           Rows Removed by Join Filter: 85\r\n                                                           ->  Seq Scan on pg_namespace n  (cost=0.00..3.19 rows=1 width=4) (actual time=0.007..0.013 rows=1 loops=1)\r\n                                                                 Filter: (nspname = 'juanignaciosl'::name)\r\n                                                                 Rows Removed by Filter: 53\r\n                                                           ->  Seq Scan on pg_constraint s  (cost=0.00..366.00 rows=91 width=8) (actual time=2.892..6.212 rows=85 loops=1)\r\n                                                                 Filter: (consrc ~~ '%_overview_constraint(%'::text)\r\n                                                                 Rows Removed by Filter: 8509\r\n                                                     ->  Index Scan using pg_class_oid_index on pg_class c_1  (cost=0.08..7.83 rows=1 width=72) (never executed)\r\n                                                           Index Cond: (oid = s.conrelid)\r\n                                                           Filter: ((NOT pg_is_other_temp_schema(relnamespace)) AND (s.connamespace = relnamespace) AND ((relkind)::text = ANY ('{r,v,m,f}'::text[])))\r\n                                               ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a  (cost=0.08..25.54 rows=15 width=8) (never executed)\r\n                                                     Index Cond: (attrelid = c_1.oid)\r\n                                                     Filter: (NOT attisdropped)\r\n                                         ->  Index Scan using pg_type_oid_index on pg_type t_1  (cost=0.08..0.11 rows=1 width=4) (never executed)\r\n                                               Index Cond: (oid = a.atttypid)\r\n                                               Filter: (typname = 'raster'::name)\r\n                           SubPlan 2\r\n                             ->  Result  (cost=0.25..428.69 rows=1 width=93) (actual time=7.028..7.028 rows=0 loops=125)\r\n                                   One-Time Filter: (current_database() = current_database())\r\n                                   ->  Nested Loop  (cost=0.25..428.69 rows=1 width=93) (actual time=7.024..7.024 rows=0 loops=125)\r\n                                         Join Filter: (c_2.relnamespace = n_1.oid)\r\n                                         ->  Nested Loop  (cost=0.25..425.50 rows=1 width=101) (actual time=7.022..7.022 rows=0 loops=125)\r\n                                               ->  Nested Loop  (cost=0.17..425.39 rows=1 width=105) (actual time=7.020..7.020 rows=0 loops=125)\r\n                                                     ->  Nested Loop  (cost=0.08..399.81 rows=1 width=109) (actual time=7.018..7.018 rows=0 loops=125)\r\n                                                           ->  Seq Scan on pg_constraint s_1  (cost=0.00..391.71 rows=1 width=101) (actual time=7.014..7.014 rows=0 loops=125)\r\n                                                                 Filter: ((consrc ~~ '%_overview_constraint(%'::text) AND ((split_part(split_part(consrc, '''::name'::text, 2), ''''::text, 2))::name = ((c.relname)::information_schema.sql_identifier)::name))\r\n                                                                 Rows Removed by Filter: 8594\r\n                                                           ->  Index Scan using pg_class_oid_index on pg_class c_2  (cost=0.08..8.09 rows=1 width=8) (never executed)\r\n                                                                 Index Cond: (oid = s_1.conrelid)\r\n                                                                 Filter: ((NOT pg_is_other_temp_schema(relnamespace)) AND (s_1.connamespace = relnamespace) AND ((relkind)::text = ANY ('{r,v,m,f}'::text[])))\r\n                                                     ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1  (cost=0.08..25.54 rows=15 width=8) (never executed)\r\n                                                           Index Cond: (attrelid = c_2.oid)\r\n                                                           Filter: (NOT attisdropped)\r\n                                               ->  Index Scan using pg_type_oid_index on pg_type t_2  (cost=0.08..0.11 rows=1 width=4) (never executed)\r\n                                                     Index Cond: (oid = a_1.atttypid)\r\n                                                     Filter: (typname = 'raster'::name)\r\n                                         ->  Seq Scan on pg_namespace n_1  (cost=0.00..3.19 rows=1 width=4) (never executed)\r\n                                               Filter: (nspname = 'juanignaciosl'::name)\r\n               ->  Nested Loop  (cost=0.11..4.86 rows=1 width=4) (never executed)\r\n                     ->  Index Scan using pg_type_oid_index on pg_type t  (cost=0.08..4.82 rows=1 width=8) (never executed)\r\n                           Index Cond: (c.reloftype = oid)\r\n                     ->  Index Only Scan using pg_namespace_oid_index on pg_namespace nt  (cost=0.03..0.03 rows=1 width=4) (never executed)\r\n                           Index Cond: (oid = t.typnamespace)\r\n                           Heap Fetches: 0\r\n Total runtime: 890.317 ms\r\n(68 rows)\r\n```\r\n\r\nQuerying `information_schema.tables` view is slow and costly. A temporal workaround is disabling quota checks, but it should be improved, since it's performed many times.\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/CartoDB/cartodb-postgresql/issues/comments/71799215","html_url":"https://github.com/CartoDB/cartodb-postgresql/issues/65#issuecomment-71799215","issue_url":"https://api.github.com/repos/CartoDB/cartodb-postgresql/issues/65","id":71799215,"user":{"login":"javisantana","id":30147,"avatar_url":"https://avatars.githubusercontent.com/u/30147?v=3","gravatar_id":"","url":"https://api.github.com/users/javisantana","html_url":"https://github.com/javisantana","followers_url":"https://api.github.com/users/javisantana/followers","following_url":"https://api.github.com/users/javisantana/following{/other_user}","gists_url":"https://api.github.com/users/javisantana/gists{/gist_id}","starred_url":"https://api.github.com/users/javisantana/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/javisantana/subscriptions","organizations_url":"https://api.github.com/users/javisantana/orgs","repos_url":"https://api.github.com/users/javisantana/repos","events_url":"https://api.github.com/users/javisantana/events{/privacy}","received_events_url":"https://api.github.com/users/javisantana/received_events","type":"User","site_admin":false},"created_at":"2015-01-28T08:44:52Z","updated_at":"2015-01-28T08:44:52Z","body":"this query gives the same results but 100x\r\n\r\n```\r\nwith raster_tables as (\r\nSELECT o_table_name, r_table_name FROM raster_overviews\r\n      WHERE o_table_schema = 'javi' AND o_table_catalog = current_database()\r\n),\r\nuser_tables as (\r\n    SELECT table_name\r\n    FROM information_schema.tables\r\n      WHERE table_catalog = current_database() AND table_schema = 'javi'\r\n        AND table_name != 'spatial_ref_sys'\r\n        AND table_name != 'cdb_tablemetadata'\r\n        AND table_type = 'BASE TABLE'\r\n),\r\ntable_cat as (\r\n    select table_name, EXISTS(select * from raster_tables where o_table_name = table_name) as is_overview, EXISTS(select * from raster_tables where r_table_name = table_name) as is_raster from user_tables\r\n),\r\n\r\nsizes as (\r\n    select COALESCE(INT8(SUM(pg_total_relation_size('\"javi\".\"' || table_name || '\"')))) s,  CASE WHEN is_overview THEN 0 WHEN is_raster then 1 else 0.5 END as mul from table_cat group by is_overview, is_raster\r\n)\r\nselect sum(s*mul) from sizes\r\n\r\n```\r\n\r\nschema_name i used is 'javi'"}}