Postgresql 全文检索测试_mybatis plus postgrep 全文检索

---

Postgresql:10.11

测试表数据量：3亿

测试使用8c 16g 服务器

---

创建一张表：

create table nt_order (

id int PRIMARY KEY,

order_id text,

order_time timestamp without time zone,

info jsonb );

CREATE SEQUENCE nt_order_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

alter table nt_order alter column id set default nextval('nt_order_id_seq');

创建索引：

brin索引：create index idx_nt_order_brin on nt_order using brin (order_time);

全文检索：create index idx_gin_nt_order_info on nt_order using gin(to_tsvector('english',info));

为什么使用brin索引？

占空间小，比btree小千倍靠上

使用brin索引适用任何场景吗？

brin索引适合范围查询（与btree索引比差距较小），brin索引主要用于高度相关的数据，比如日期，其它类型有测试结果反映出来效果较差

造数据：

数据结构

记录行数：

数据表大小：

索引表大小：

测试：

1、全文检索，where组合条件查询(in语句查询) 

SELECT id,order_time FROM nt_order WHERE to_tsvector('English', info) @@ to_tsquery('English','org^1 | org^2 | org^3') and to_tsvector('English', info) @@ to_tsquery('English','Aaron | Abbott | Abel | Abner | Abraham | Adair | Adam | Adolph | Adonis | Adrian | Ahern | Alan | Albert | Aldrich | Alexander | Alfred | Alger | Algernon | Allen | Alston | Alva | Alvis | Amos | Andre | Andrew | Andy | Angelo | Augus | Ansel | Antony | Antoine | Antonio | Archer | Archibald | Aries | Arlen | Armand | Armstrong | Arno | Arnold | Arthur | Arvin | Asa | Ashbur | Atwood | Aubrey | August | Augustine | Avery | Baird | Baldwin | Bancroft | Bard | Barlow | Barnett | Baron | Barret | Barry | Bartholomew | Bart | Barton | Bartley | Basil | Beacher | Beau | Beck | Ben | Benedict | Benjamin | Bennett | Benson | Berg | Berger | Bernard | Bernie | Bert | Berton | Bertram | Bevis | Bill | Bing | Bishop | Blair | Blake | BliTHE | Bob | Booth | Borg | Boris | Bowen | Boyce | Boyd | Bradley | Brady | Brandon | Brian | Broderick | Brook | Bruce | Bruno | Buck | Burgess | Burke | Burnell | Burton | Byron | Caesar | Calvin | Carey | Carl | Carr | Carter | Cash | Cecil | Cedric | Chad | Channing | Chapman | Charles | Chasel | Chester | Christian | Christopher | Clare | Clarence | Clark | Claude | Clement | Cleveland | Cliff | Clifford | Clyde | Colbert | Colby | Colin | Conrad | Corey | Cornelius | Craig | Curitis | Cyril | Dana | Daniel | Darcy | Darnell | Darren | Dave | David | Dean | Dempsey | Dennis | Derrick | Devin | Dick | Dominic | Don | Donahue | Donald | Drew | Duke | Duncan | Dunn | Dwight | Dylan | Earl | Ed | Eden | Edgar | Edmund | Edison | Edward | Edwiin | Egbert | Eli | Elijah | Elliot | Ellis | Elmer | Elroy | Elton | Elvis | Emmanuel | Enoch | Eric | Ernest | Eugene | Evan | Fabian | Felix | Fitzgerald | Ford | Frank | Franklin | Frederic | Gale | Gary | Gavin | Gene | Geoffrey | Geoff | George | Gerald | Giles | Glenn | Gordon | GREg | Griffith | Grover | Guy | Hale | Haley | Hamiltion | Hardy | Harlan | Harley | Harold | Harry | Harvey | Hayden | Henry | Herman | Hilary | Hiram | Hobart | Hogan | Horace | Howar | Hubery | Hugh | Hugo | Humphrey | Hunter | Hyman | Ian | Ingram | Ira | Isaac | Ivan | Ives | Jack | Jacob | James | Jared | Jason | Jay | Jeff | Jeremy | Jerome | Jerry | Jesse | Jim | Jo | John | Jonas | Jonathan | Joseph | Joshua | Joyce | Julian | Julius | Justin | Keith | Kelly | Ken | Kent | Kerr | Kerwin | Kim | King | Kirk | Kyle | Lance | Larry | Lawrence | Leif | Len | Lennon | Leo | Les | Lester | Levi | Lewis | Lionel | Lou | Louis | Lucien | LuTHEr | Lyle | Lyndon | Lynn | Magee | Mandel | Marcus | Marico | Marlon | Marsh | Marshall | Martin | Marvin | Matt | Max | Maximilian | Meredith | Merle | Merlin | Mick | Mike | Miles | Milo | Monroe | Montague | Moore | Morgan | Mortimer | Morton | Moses | Murphy | Murray | Myron | Nat | Nathan | Nathaniel | Neil | Nelson | Newman | Nicholas | Nick | Nigel | Noah | Noel | Norman | Norton | Ogden | Oliver | Omar | Osborn | Oscar | Osmond | Oswald | Otis | Otto | Owen | Page | Parker | Paddy | Paul | Payne | Perry | Pete | Peter | Phil | Philip | Porter | Prescott | Primo | Quincy | Quinn | Quintion | Rachel | Ralap | Randolph | Reg | Regan | Reginald | Reuben | Rex | Robert | Robin | Rock | Rod | Roderick | Rodney | Ron | Ronald | Rory | Roy | Rudolf | Rupert | Ryan | Sam | Samuel | Sandy | Saxon | Scott | Sean | Sebastian | Sid | Sidney | Silvester | Simon') and order_time between '2019-12-12 19:30:49' and '2019-12-12 19:32:49' limit 20 offset 10; 

2、加排序

小结：查询速度相对较快，但是加上order by会明显拖慢整个速度，下表是加order by和不加order by的速度对比