Staging instance, all changes can be removed at any time

Skip to content
Snippets Groups Projects

origin search query weirdness

The snippet can be accessed without any authentication.
Authored by David Douard
Edited
snippetfile1.txt 1.32 KiB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 
```
explain analyze WITH filtered_origins AS (
                SELECT *
                FROM origin
                WHERE url ILIKE '%enea.it%' order by id) 
                SELECT * from filtered_origins offset 0 ;
QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=35418.77..35486.74 rows=27188 width=53) (actual time=826.724..826.738 rows=5 loops=1)
   Sort Key: origin.id
   Sort Method: quicksort  Memory: 25kB
   ->  Bitmap Heap Scan on origin  (cost=1129.90..33416.28 rows=27188 width=53) (actual time=826.639..826.693 rows=5 loops=1)
         Recheck Cond: (url ~~* '%enea.it%'::text)
         Rows Removed by Index Recheck: 10
         Heap Blocks: exact=15
         ->  Bitmap Index Scan on origin_url_idx1  (cost=0.00..1123.11 rows=27188 width=0) (actual time=826.540..826.542 rows=15 loops=1)
               Index Cond: (url ~~* '%enea.it%'::text)
 Planning Time: 9.815 ms
 Execution Time: 826.817 ms
(11 rows)

explain analyze WITH filtered_origins AS (
                SELECT *
                FROM origin
                WHERE url ILIKE '%enea.it%' order by id) 
                SELECT * from filtered_origins offset 0 limit 51;
[stuck for 10s of seconds]
```
  • David Douard @douardda ·

    ah here is the stuck one:

    --------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1000.60..13300.87 rows=51 width=53) (actual time=322778.856..324304.270 rows=5 loops=1)
   ->  Gather Merge  (cost=1000.60..6557979.50 rows=27188 width=53) (actual time=322778.842..324304.249 rows=5 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Parallel Index Scan using origin_pkey on origin  (cost=0.57..6553841.30 rows=11328 width=53) (actual time=125386.510..264964.660 rows=2 loops=3)
               Filter: (url ~~* '%enea.it%'::text)
               Rows Removed by Filter: 90787357
 Planning Time: 0.277 ms
 Execution Time: 324304.307 ms
(9 rows)
0% or .
You are about to add 0 people to the discussion. Proceed with caution.
Finish editing this message first!
Please register or to comment