![\"twitter\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" "\\"Share")
<\/a><\/a>![\"linkedin\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" "\\"Share")
<\/a>![\"mail\"](\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" "\\"Share")
<\/a>La possibilit\u00e9 de partitionnement natif de tables a \u00e9t\u00e9 introduite en version 10 de PostgreSQL. C’\u00e9tait possible auparavant mais par le biais de la cr\u00e9ation de tables par h\u00e9ritage (clause INHERIT). Avec l’ex\u00e9cution en parall\u00e9lisation de requ\u00eates SQL (introduite en v9.6), cela a constitu\u00e9 une \u00e9volution majeure pour adresser les probl\u00e9matiques d’acc\u00e8s et de gestion de donn\u00e9es tr\u00e8s volumineuses.<\/p>\n
je ne reviendrai pas sur la m\u00e9thode pour partitionner des tables avec PostgreSQL, et les \u00e9volutions apport\u00e9es \u00e0 cette fonctionnalit\u00e9 depuis la v10. Je vous renvoie pour cela aux articles PostgreSQL : \u00e9volution du partitionnement de 9.6 \u00e0 12 (1\/2)<\/a> et\u00a0PostgreSQL : \u00e9volution du partitionnement de 9.6 \u00e0 12 (2\/2)<\/a> sur le sujet.<\/p>\n Avant de se lancer t\u00eate baiss\u00e9e dans le partitionnement des plus grosses tables de sa base de donn\u00e9es, il faut consid\u00e9rer ces facteurs essentiels pour faire les bons choix :<\/p>\n La cl\u00e9 sera naturelle si elle suit la logique dans l’organisation des donn\u00e9es et si elle correspond \u00e0 un crit\u00e8re majeur et r\u00e9p\u00e9t\u00e9 dans les pr\u00e9dicats des requ\u00eates des utilisateurs.<\/p>\n La cl\u00e9 peut \u00eatre une colonne de type date ou timestamp correspondant \u00e0 la date\/heure de l’\u00e9v\u00e9nement enregistr\u00e9 dans la table ou une autre cl\u00e9 qui suivrait la logique de l’organisation (cl\u00e9 d’une filiale ou d’une zone g\u00e9ographique suivant une logique d’acc\u00e8s des utilisateurs \u00e0 leurs donn\u00e9es locales). Le partitionnement \u00e9tant possible sur plusieurs niveaux, on peut aussi partitionner et sous-partitionner suivant 2 cl\u00e9s.<\/p>\n Une fois les cl\u00e9s retenues et une ou plusieurs tables migr\u00e9es en tables partitionn\u00e9es, l’autre question majeure consiste \u00e0 savoir si mes requ\u00eates sauront tirer le b\u00e9n\u00e9fice de ce partitionnement.<\/strong><\/p>\n En effet, \u00e0 travers le plan d’ex\u00e9cution de la requ\u00eate d\u00e9termin\u00e9 par le “planner” de PostgreSQL , il s’agit de pouvoir lire les seules partitions utiles<\/em> et pas la totalit\u00e9 de la table partitionn\u00e9e (\u00e9limination de partitions\u00a0 ou “pruning” qu’on peut traduire litt\u00e9ralement par “\u00e9lagage”)<\/b>. L’\u00e9criture des requ\u00eates doit permettre \u00e0 PostgreSQL de faire cette \u00e9limination d\u00e8s l’\u00e9laboration du plan d’ex\u00e9cution de la requ\u00eate, sinon \u00e0 l’ex\u00e9cution de celle-ci (possibilit\u00e9 de “pruning” pendant l’ex\u00e9cution introduite en version 11).<\/p>\n Prenons un exemple sur la toute derni\u00e8re version 13 de PostgreSQL, avec le sch\u00e9ma “pgbench<\/strong>” dans lequel j’ai\u00a0 cr\u00e9\u00e9 une table partitionn\u00e9e\u00a0 “pgbench_history_part<\/strong>” \u00e0 partir des donn\u00e9es de pgbench_history suivant deux cl\u00e9s:<\/p>\n Description d’une des 10 partitions et son sous-partitionnement par RANGE sur mtime, un RANGE correspondant \u00e0 une ann\u00e9e (donn\u00e9es artificiellement cr\u00e9\u00e9es pour couvrir 20 ann\u00e9es de fa\u00e7on \u00e9gale pour les 10 valeurs de bid):<\/p>\n Je n’ai cr\u00e9\u00e9 qu’un seul index pgbench_history_part_mtime_idx sur (mtime), index propag\u00e9 sur chaque partition.<\/p>\n Imaginons vouloir \u00e9crire un simple SELECT en jointure sur pgbench_history_part et pgbench_branches o\u00f9 on souhaite s\u00e9lectionner les donn\u00e9es de pgbench_history_part d’une ou plusieurs branches sans connaissance des ID de ces branches (bid) mais avec pr\u00e9dicat sur bbalance, colonne de la table jointe pgbench_branches,\u00a0 :<\/p>\n Le plan d’ex\u00e9cution (EXPLAIN ANALYZE) montre que toutes les partitions ont \u00e9t\u00e9 lues, avec une dur\u00e9e d’ex\u00e9cution de 279 secondes\u00a0 pour rows=10016860 lignes retourn\u00e9es ! Nous remarquons au passage que ce nombre de ligne r\u00e9ellement lues (actual rows) est 2 fois sup\u00e9rieur \u00e0 l’estimation rows=500515.<\/p>\n Essayons une autre approche en rempla\u00e7ant la jointure explicite par un SOUS SELECT permettant un pr\u00e9dicat sur h.bid :<\/p>\n Le plan est le m\u00eame avec parcours de toutes les partitions et m\u00eame dur\u00e9e.<\/p>\n En fait, nous savons que chaque branche a sa valeur bbalance unique :<\/p>\n Ainsi, l’op\u00e9rateur d’\u00e9galit\u00e9 est possible dans ce contexte particulier de mes donn\u00e9es :<\/p>\n Le nombre de lignes retourn\u00e9es est le m\u00eame avec cette fois une estimation conforme \u00e0 la r\u00e9alit\u00e9. Le plan d’ex\u00e9cution semble identique, mais la diff\u00e9rence essentielle r\u00e9side dans le fait que des lectures de partitions ne sont pas ex\u00e9cut\u00e9es (never executed)<\/strong>. C’est une application du “pruning” r\u00e9alis\u00e9 \u00e0 l’ex\u00e9cution<\/strong>.<\/p>\n Le temps d’ex\u00e9cution est logiquement divis\u00e9 par 10 avec 28 secondes.<\/p>\n Ces SQL simples listant directement les cl\u00e9s “bid” avec op\u00e9rateur IN ou BETWEEN vont \u00e9videmment permettre le pruning, d\u00e9termin\u00e9 cette fois d\u00e8s l’\u00e9laboration du plan d’ex\u00e9cution:<\/p>\n Pour r\u00e9sumer, le pruning sera possible \u00e0 l’ex\u00e9cution si :<\/strong><\/p>\n Il ne fonctionnera pas toujours dans le cas o\u00f9 :<\/strong><\/p>\n Int\u00e9ressons nous maintenant au second niveau de partitionnement par RANGE<\/strong> de la colonne mtime<\/strong>, en ajoutant une clause BETWEEN sur mtime pour s\u00e9lectionner des donn\u00e9es sur une plage mtime de 10 minutes seulement le 03\/11\/2013 :<\/p>\n Le plan d’ex\u00e9cution r\u00e9v\u00e8le un “pruning” permettant de ne lire qu’une seule partition, celle de l’ann\u00e9e 2013 de la branche 6, par “Index Scan” avec l’index cr\u00e9\u00e9 sur mtime (“Index Scan using pgbench_history_part_6_2013_mtime_idx on pgbench_history_part_6_2013”). Le pruning \u00e0 l’\u00e9laboration du plan permettait de savoir que seules les partitions “2013” seraient utiles du fait des valeurs explicites dans le pr\u00e9dicat sur mtime, et c’est ensuite \u00e0 l’ex\u00e9cution seulement que celles des branches autres que bid=6 ont pu aussi \u00eatre \u00e9limin\u00e9es (never executed<\/em>) apr\u00e8s r\u00e9solution de la valeur retourn\u00e9e par le SOUS SELECT. La dur\u00e9e d’ex\u00e9cution est cette fois de 30 ms !<\/p>\n Evidemment, le b\u00e9n\u00e9fice sera le m\u00eame avec op\u00e9rateurs <, > ou = sur mtime :<\/p>\n Par contre, toute transformation appliqu\u00e9e au champ mtime ne permettra pas de pruning, pas plus qu’il ne rendra possible l’utilisation de l’index, par exemple en ajoutant un intervalle \u00e0 mtime :<\/p>\n PostgreSQL utilise les contraintes de LIST ou RANGE d\u00e9finissant les partitions pour d\u00e9terminer les conditions permettant d’\u00e9liminer des partitions.<\/p>\n Avec les am\u00e9liorations successives du pruning, que le partitionnement soit par RANGE, LIST ou HASH, de nombreux op\u00e9rateurs de comparaison sur la cl\u00e9 de partitionnement permettent l’\u00e9limination de partitions d\u00e8s l’\u00e9tape de planification<\/strong>, que ce soit avec =, >, <,<\/strong> BETWEEN ou encore IN (liste de valeurs<\/em>); <\/strong>du moment que les valeurs soient interpr\u00e9tables \u00e0 la planification.<\/p>\n Dans le cas o\u00f9 les valeurs de la cl\u00e9 ne peuvent \u00eatre d\u00e9termin\u00e9es qu’\u00e0 l’ex\u00e9cution, le pruning possible \u00e0 l’ex\u00e9cution d\u00e9pendra beaucoup plus de l’\u00e9criture des jointures ou\/et sous-requ\u00eates dans les pr\u00e9dicats…<\/p>\n Au sujet du pruning dans la documentation officielle :\u00a0https:\/\/www.postgresql.org\/docs\/13\/ddl-partitioning.html#DDL-PARTITION-PRUNING<\/a>\u00a0<\/strong><\/em><\/p>\n La possibilit\u00e9 de partitionnement natif de tables a \u00e9t\u00e9 introduite en version 10 de PostgreSQL. C’\u00e9tait possible auparavant mais par le biais de la cr\u00e9ation de tables par h\u00e9ritage (clause INHERIT). Avec l’ex\u00e9cution en parall\u00e9lisation de requ\u00eates SQL (introduite en… Continuer la lecture \n
\n
pgbench=# \\d+ pgbench_history_part\r\nPartitioned table "public.pgbench_history_part"\r\nColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description\r\n--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------\r\ntid | integer | | | | plain | |\r\nbid | integer | | | | plain | |\r\naid | integer | | | | plain | |\r\ndelta | integer | | | | plain | |\r\nmtime | timestamp without time zone | | | | plain | |\r\nfiller | character(22) | | | | extended | |\r\nPartition key: LIST (bid)\r\nIndexes:\r\n"pgbench_history_part_mtime_idx" btree (mtime)\r\nPartitions: pgbench_history_part_1 FOR VALUES IN (1), PARTITIONED,\r\npgbench_history_part_10 FOR VALUES IN (10), PARTITIONED,\r\npgbench_history_part_2 FOR VALUES IN (2), PARTITIONED,\r\npgbench_history_part_3 FOR VALUES IN (3), PARTITIONED,\r\npgbench_history_part_4 FOR VALUES IN (4), PARTITIONED,\r\npgbench_history_part_5 FOR VALUES IN (5), PARTITIONED,\r\npgbench_history_part_6 FOR VALUES IN (6), PARTITIONED,\r\npgbench_history_part_7 FOR VALUES IN (7), PARTITIONED,\r\npgbench_history_part_8 FOR VALUES IN (8), PARTITIONED,\r\npgbench_history_part_9 FOR VALUES IN (9), PARTITIONED<\/pre>\n
pgbench=# \\d+ pgbench_history_part_1\r\nPartitioned table "public.pgbench_history_part_1"\r\nColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description\r\n--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------\r\ntid | integer | | | | plain | |\r\nbid | integer | | | | plain | |\r\naid | integer | | | | plain | |\r\ndelta | integer | | | | plain | |\r\nmtime | timestamp without time zone | | | | plain | |\r\nfiller | character(22) | | | | extended | |\r\nPartition of: pgbench_history_part FOR VALUES IN (1)\r\nPartition constraint: ((bid IS NOT NULL) AND (bid = 1))\r\nPartition key: RANGE (mtime)\r\nIndexes:\r\n"pgbench_history_part_1_mtime_idx" btree (mtime)\r\nPartitions: pgbench_history_part_1_2001 FOR VALUES FROM ('2001-01-01 00:00:00') TO ('2002-01-01 00:00:00'),\r\npgbench_history_part_1_2002 FOR VALUES FROM ('2002-01-01 00:00:00') TO ('2003-01-01 00:00:00'),\r\npgbench_history_part_1_2003 FOR VALUES FROM ('2003-01-01 00:00:00') TO ('2004-01-01 00:00:00'),\r\npgbench_history_part_1_2004 FOR VALUES FROM ('2004-01-01 00:00:00') TO ('2005-01-01 00:00:00'),\r\npgbench_history_part_1_2005 FOR VALUES FROM ('2005-01-01 00:00:00') TO ('2006-01-01 00:00:00'),\r\npgbench_history_part_1_2006 FOR VALUES FROM ('2006-01-01 00:00:00') TO ('2007-01-01 00:00:00'),\r\npgbench_history_part_1_2007 FOR VALUES FROM ('2007-01-01 00:00:00') TO ('2008-01-01 00:00:00'),\r\npgbench_history_part_1_2008 FOR VALUES FROM ('2008-01-01 00:00:00') TO ('2009-01-01 00:00:00'),\r\npgbench_history_part_1_2009 FOR VALUES FROM ('2009-01-01 00:00:00') TO ('2010-01-01 00:00:00'),\r\npgbench_history_part_1_2010 FOR VALUES FROM ('2010-01-01 00:00:00') TO ('2011-01-01 00:00:00'),\r\npgbench_history_part_1_2011 FOR VALUES FROM ('2011-01-01 00:00:00') TO ('2012-01-01 00:00:00'),\r\npgbench_history_part_1_2012 FOR VALUES FROM ('2012-01-01 00:00:00') TO ('2013-01-01 00:00:00'),\r\npgbench_history_part_1_2013 FOR VALUES FROM ('2013-01-01 00:00:00') TO ('2014-01-01 00:00:00'),\r\npgbench_history_part_1_2014 FOR VALUES FROM ('2014-01-01 00:00:00') TO ('2015-01-01 00:00:00'),\r\npgbench_history_part_1_2015 FOR VALUES FROM ('2015-01-01 00:00:00') TO ('2016-01-01 00:00:00'),\r\npgbench_history_part_1_2016 FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'),\r\npgbench_history_part_1_2017 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'),\r\npgbench_history_part_1_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),\r\npgbench_history_part_1_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-12-31 00:00:00'),\r\npgbench_history_part_1_2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')\r\n<\/pre>\n\r\nselect h.* from pgbench_history_part h JOIN pgbench_branches b ON h.bid=b.bid \r\nWHERE b.bbalance=-1060874;\r\n<\/pre>\n
\r\npgbench=# explain analyse select h.* from pgbench_history_part h JOIN pgbench_branches b \r\nON h.bid=b.bid WHERE b.bbalance=-1060874;\r\n\r\n QUERY PLAN \r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Gather (cost=1014.14..1426216.66 rows=500515 width=116) (actual time=227398.538..269726.289 rows=10016860 loops=1)\r\n Workers Planned: 2\r\n Workers Launched: 2\r\n Hash Join (cost=14.14..1375165.16 rows=208548 width=116) (actual time=151981.556..188122.355 rows=3338953 loops=3)\r\n Hash Cond: (h.bid = b.bid)\r\n Parallel Append (cost=0.00..1263343.33 rows=41709561 width=116) (actual time=0.700..134063.520 rows=33367642 loops=3)\r\n Parallel Seq Scan on pgbench_history_part_8_2001 h_141 (cost=0.00..5284.98 rows=208998 width=116) (actual time=0.932..1047.692 rows=501596 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_8_2002 h_142 (cost=0.00..5284.98 rows=208998 width=116) (actual time=0.493..1225.944 rows=501596 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_8_2003 h_143 (cost=0.00..5284.98 rows=208998 width=116) (actual time=7.471..970.286 rows=501596 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_8_2004 h_144 (cost=0.00..5284.98 rows=208998 width=116) (actual time=0.917..1114.619 rows=501596 loops\r\n\t\t\t ...\r\n\t\t Parallel Seq Scan on pgbench_history_part_3_2016 h_56 (cost=0.00..5254.75 rows=207775 width=116) (actual time=0.010..927.365 rows=498661 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_3_2017 h_57 (cost=0.00..5254.75 rows=207775 width=116) (actual time=8.567..1025.146 rows=498661 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_3_2018 h_58 (cost=0.00..5254.75 rows=207775 width=116) (actual time=3.004..1164.583 rows=498661 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_3_2019 h_59 (cost=0.00..5254.75 rows=207775 width=116) (actual time=3.489..1096.999 rows=498661 loops=1)\r\n Parallel Seq Scan on pgbench_history_part_3_2020 h_60 (cost=0.00..5254.75 rows=207775 width=116) (actual time=0.668..1026.125 rows=498661 loops=1)\r\n Hash (cost=14.12..14.12 rows=1 width=4) (actual time=422.343..422.347 rows=1 loops=3)\r\n Buckets: 1024 Batches: 1 Memory Usage: 9kB\r\n Seq Scan on pgbench_branches b (cost=0.00..14.12 rows=1 width=4) (actual time=422.323..422.336 rows=1 loops=3)\r\n Filter: (bbalance = '-1060874'::integer)\r\n Rows Removed by Filter: 9\r\n Planning Time: 4.387 ms\r\n JIT:\r\n Functions: 27\r\n Options: Inlining true, Optimization true, Expressions true, Deforming true\r\n Timing: Generation 13.267 ms, Inlining 423.653 ms, Optimization 484.481 ms, Emission 356.528 ms, Total 1277.928 ms\r\n Execution Time: 279848.557 ms\r\n(217 rows)\r\n<\/pre>\n
\r\nexplain analyse select h.* from pgbench_history_part h\r\nWHERE h.bid IN (SELECT b.bid FROM pgbench_branches b\r\nWHERE b.bbalance=-1060874);\r\n<\/pre>\n
\r\npgbench=# select * from pgbench_branches ;\r\nbid | bbalance | filler\r\n-----+----------+--------\r\n9 | 2420106 |\r\n10 | 2947968 |\r\n2 | 7995538 |\r\n6 | -1060874 |\r\n3 | -539711 |\r\n7 | -2102106 |\r\n4 | 2273395 |\r\n5 | -2919603 |\r\n1 | -1734545 |\r\n8 | -649184 |\r\n(10 rows)\r\n<\/pre>\n
SELECT h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874); <\/pre>\n
\r\npgbench=# explain analyse select h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874);\r\n\r\nQUERY PLAN\r\n--------------------------------------------------------------------------------------------------------------------------------------------------\r\nAppend (cost=14.12..2389515.31 rows=100102925 width=116) (actual time=3962.001..22765.583 rows=10016860 loops=1)\r\nInitPlan 1 (returns $0)\r\n Seq Scan on pgbench_branches b (cost=0.00..14.12 rows=1 width=4) (actual time=0.055..0.063 rows=1 loops=1)\r\nFilter: (bbalance = '-1060874'::integer)\r\nRows Removed by Filter: 9\r\n Seq Scan on pgbench_history_part_1_2001 h_1 (cost=0.00..9452.89 rows=500951 width=116) (never executed)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_1_2002 h_2 (cost=0.00..9452.89 rows=500951 width=116) (never executed)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_1_2003 h_3 (cost=0.00..9452.89 rows=500951 width=116) (never executed)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_1_2004 h_4 (cost=0.00..9452.92 rows=500954 width=116) (never executed)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_1_2005 h_5 (cost=0.00..9452.91 rows=500953 width=116) (never executed)\r\nFilter: (bid = $0)\r\n...\r\n Seq Scan on pgbench_history_part_6_2005 h_105 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.616..353.166 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2006 h_106 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.717..348.679 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2007 h_107 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.518..349.334 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2008 h_108 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.572..354.518 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2009 h_109 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.595..351.174 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2010 h_110 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.761..348.445 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2011 h_111 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.619..347.263 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2012 h_112 (cost=0.00..9451.54 rows=500843 width=116) (actual time=2.862..350.480 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2013 h_113 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.541..345.805 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2014 h_114 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.528..347.940 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2015 h_115 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.802..348.066 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2016 h_116 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.706..346.924 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2017 h_117 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.589..349.294 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2018 h_118 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.889..346.096 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2019 h_119 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.554..347.696 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_6_2020 h_120 (cost=0.00..9451.54 rows=500843 width=116) (actual time=0.562..350.516 rows=500843 loops=1)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_7_2001 h_121 (cost=0.00..9436.80 rows=500064 width=116) (never executed)\r\nFilter: (bid = $0)\r\n Seq Scan on pgbench_history_part_7_2002 h_122 (cost=0.00..9436.80 rows=500064 width=116) (never executed)\r\n...\r\nPlanning Time: 4.950 ms\r\nJIT:\r\nFunctions: 405\r\nOptions: Inlining true, Optimization true, Expressions true, Deforming true\r\nTiming: Generation 40.662 ms, Inlining 9.622 ms, Optimization 2439.467 ms, Emission 1507.671 ms, Total 3997.423 ms\r\nExecution Time: 28649.118 ms\r\n(411 rows)\r\n<\/pre>\n
\r\nselect h.* from pgbench_history_part h WHERE h.bid IN (3,7);\r\nselect h.* from pgbench_history_part h WHERE h.bid between 3 and 5;\r\n<\/pre>\n
\n
\n
SELECT h.* from pgbench_history_part h \r\nWHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874) \r\nAND mtime BETWEEN '2013-11-03 12:00:00'::timestamp AND '2013-11-03 12:10:00'::timestamp; <\/pre>\n
\r\nQUERY PLAN\r\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\nAppend (cost=14.55..4727.49 rows=103349 width=116) (actual time=0.074..23.478 rows=11335 loops=1)\r\nInitPlan 1 (returns $0)\r\n Seq Scan on pgbench_branches b (cost=0.00..14.12 rows=1 width=4) (actual time=0.029..0.038 rows=1 loops=1)\r\nFilter: (bbalance = '-1060874'::integer)\r\nRows Removed by Filter: 9\r\n Index Scan using pgbench_history_part_1_2013_mtime_idx on pgbench_history_part_1_2013 h_1 (cost=0.42..439.77 rows=10905 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_2_2013_mtime_idx on pgbench_history_part_2_2013 h_2 (cost=0.42..439.75 rows=10860 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_3_2013_mtime_idx on pgbench_history_part_3_2013 h_3 (cost=0.42..419.00 rows=10311 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_4_2013_mtime_idx on pgbench_history_part_4_2013 h_4 (cost=0.42..450.14 rows=11140 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_5_2013_mtime_idx on pgbench_history_part_5_2013 h_5 (cost=0.42..375.25 rows=9183 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_6_2013_mtime_idx on pgbench_history_part_6_2013 h_6 (cost=0.42..422.91 rows=10425 width=116) (actual time=0.030..10.050 rows=11335 loops=1)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_7_2013_mtime_idx on pgbench_history_part_7_2013 h_7 (cost=0.42..434.29 rows=10677 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_8_2013_mtime_idx on pgbench_history_part_8_2013 h_8 (cost=0.42..419.51 rows=10312 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_9_2013_mtime_idx on pgbench_history_part_9_2013 h_9 (cost=0.42..393.85 rows=9695 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\n Index Scan using pgbench_history_part_10_2013_mtime_idx on pgbench_history_part_10_2013 h_10 (cost=0.42..402.16 rows=9841 width=116) (never executed)\r\nIndex Cond: ((mtime >= '2013-11-03 12:00:00'::timestamp without time zone) AND (mtime <= '2013-11-03 12:10:00'::timestamp without time zone))\r\nFilter: (bid = $0)\r\nPlanning Time: 0.796 ms\r\nExecution Time: 30.138 ms\r\n(37 rows)\r\n<\/pre>\n
\r\nSELECT h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874) AND mtime = '2013-11-03 12:00:00'::timestamp ;\r\n<\/pre>\n
\r\nSELECT h.* from pgbench_history_part h WHERE h.bid = (SELECT b.bid FROM pgbench_branches b WHERE b.bbalance=-1060874) AND mtime + interval '1 hour' = '2013-11-03 12:00:00'::timestamp ;\r\n<\/pre>\n
<\/a><\/a><\/a>","protected":false},"excerpt":{"rendered":"