=================================================================================== ===== The data test1=# \d pglist Table "public.pglist" Column | Type | Collation | Nullable | Default ------------+-----------------------------+-----------+----------+--------- id | integer | | | sent | timestamp without time zone | | | subject | text | | | author | text | | | body_plain | text | | | fts | tsvector | | | Indexes: "idx_pglist_rum_fts" rum (fts) "idx_pglist_fts" gin (fts) "idx_pglist_sent" btree (sent) test1=# select min(sent), max(sent), count(*) from pglist; min | max | count ---------------------+---------------------+--------- 1997-06-24 11:31:09 | 2016-04-27 23:46:29 | 1013770 (1 row) =================================================================================== ===== RUM sample -- count(*) with bitmap count: test1=# set work_mem to 8192; SET test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Count on pglist (cost=550.65..1095.68 rows=54503 width=8) (actual time=1120.281..1120.281 rows=1 loops=1) Recheck Cond: (fts @@ to_tsquery('tom & lane'::text)) Heap Fetches: 0 Heap Blocks: exact=105992 -> Bitmap Index Scan on idx_pglist_rum_fts (cost=0.00..537.02 rows=54503 width=0) (actual time=1056.060..1056.060 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 119.568 ms Execution time: 1121.409 ms (8 rows) -- count(*) with bitmap heap scan: test1=# set work_mem to 8192; SET test1=# set enable_bitmapcount to off; SET test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=119997.73..119997.74 rows=1 width=8) (actual time=8797.477..8797.477 rows=1 loops=1) -> Gather (cost=119997.52..119997.73 rows=2 width=8) (actual time=8795.510..8797.466 rows=3 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial Aggregate (cost=118997.52..118997.53 rows=1 width=8) (actual time=8773.299..8773.299 rows=1 loops=3) -> Parallel Bitmap Heap Scan on pglist (cost=550.65..118940.74 rows=22710 width=0) (actual time=1055.664..8744.935 rows=74271 loops=3) Recheck Cond: (fts @@ to_tsquery('tom & lane'::text)) Heap Blocks: exact=35562 -> Bitmap Index Scan on idx_pglist_rum_fts (cost=0.00..537.02 rows=54503 width=0) (actual time=1013.974..1013.974 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 96.848 ms Execution time: 8806.806 ms (12 rows) -- first 20 results: test1=# explain analyze select subject from pglist where fts @@ to_tsquery( 'tom & lane' ) order by fts <=> to_tsquery( 'tom & lane' ) limit 20; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=48.50..122.65 rows=20 width=45) (actual time=1550.934..1727.652 rows=20 loops=1) -> Index Scan using idx_pglist_rum_fts on pglist (cost=48.50..202112.57 rows=54503 width=45) (actual time=1550.932..1727.617 rows=20 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Order By: (fts <=> to_tsquery('tom & lane'::text)) Planning time: 144.217 ms Execution time: 1734.868 ms =================================================================================== == GIN sample -- count(*) with bitmap count: test1=# set work_mem to 8192; SET test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ) and sent between '2011/03/05'::date and '2012/03/05'::date; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Count on pglist (cost=1628.65..1656.69 rows=2804 width=8) (actual time=587.395..587.395 rows=1 loops=1) Recheck Cond: ((sent >= '2011-03-05'::date) AND (sent <= '2012-03-05'::date) AND (fts @@ to_tsquery('tom & lane'::text))) Heap Fetches: 0 Heap Blocks: exact=5437 -> BitmapAnd (cost=1628.65..1628.65 rows=2804 width=0) (actual time=569.118..569.118 rows=0 loops=1) -> Bitmap Index Scan on idx_pglist_sent (cost=0.00..1097.97 rows=52155 width=0) (actual time=29.842..29.842 rows=52148 loops=1) Index Cond: ((sent >= '2011-03-05'::date) AND (sent <= '2012-03-05'::date)) -> Bitmap Index Scan on idx_pglist_fts (cost=0.00..529.02 rows=54503 width=0) (actual time=532.539..532.539 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) Planning time: 121.668 ms Execution time: 592.077 ms (11 rows) -- count(*) with bitmap heap scan: test1=# set work_mem to 8192; SET test1=# set enable_bitmapcount to off; SET test1=# explain analyze select count(*) from pglist where fts @@ to_tsquery( 'tom & lane' ) and sent between '2011/03/05'::date and '2012/03/05'::date; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=12427.03..12427.04 rows=1 width=8) (actual time=2162.997..2162.998 rows=1 loops=1) -> Bitmap Heap Scan on pglist (cost=1628.65..12420.02 rows=2804 width=0) (actual time=599.564..2157.119 rows=10673 loops=1) Recheck Cond: ((fts @@ to_tsquery('tom & lane'::text)) AND (sent >= '2011-03-05'::date) AND (sent <= '2012-03-05'::date)) Heap Blocks: exact=5437 -> BitmapAnd (cost=1628.65..1628.65 rows=2804 width=0) (actual time=592.798..592.798 rows=0 loops=1) -> Bitmap Index Scan on idx_pglist_fts (cost=0.00..529.02 rows=54503 width=0) (actual time=528.937..528.937 rows=222813 loops=1) Index Cond: (fts @@ to_tsquery('tom & lane'::text)) -> Bitmap Index Scan on idx_pglist_sent (cost=0.00..1097.97 rows=52155 width=0) (actual time=44.796..44.796 rows=52148 loops=1) Index Cond: ((sent >= '2011-03-05'::date) AND (sent <= '2012-03-05'::date)) Planning time: 139.393 ms Execution time: 2169.647 ms (11 rows) -- first 20 results: test1=# explain analyze select subject from pglist where fts @@ to_tsquery( 'tom & lane' ) and sent between '2011/03/05'::date and '2012/03/05'::date order by sent limit 20; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.42..1112.46 rows=20 width=49) (actual time=27.764..305.854 rows=20 loops=1) -> Index Scan using idx_pglist_sent on pglist (cost=0.42..155907.40 rows=2804 width=49) (actual time=27.761..305.826 rows=20 loops=1) Index Cond: ((sent >= '2011-03-05'::date) AND (sent <= '2012-03-05'::date)) Filter: (fts @@ to_tsquery('tom & lane'::text)) Rows Removed by Filter: 75 Planning time: 148.445 ms Execution time: 306.383 ms (7 rows)