Spójrzcie na ten query plan:
QUERY PLAN
HashAggregate (cost=66.50..67.87 rows=137 width=9) (actual time=1.923..1.940 rows=51 loops=1)
Group Key: user_id, robot
Buffers: shared hit=31
-> Seq Scan on sessions (cost=0.00..56.25 rows=1366 width=9) (actual time=0.017..1.227 rows=1360 loops=1)
Filter: ((path)::text ~~ '/forum/%'::text)
Rows Removed by Filter: 660
Buffers: shared hit=31
Planning time: 0.860 ms
Execution time: 1.988 ms
Spójrzcie na: Execution time: 1.988 ms
Teraz wykonując zapytanie bez EXPLAIN ANALYSE
na początku: 50 rows retrieved starting from 1 in 98ms (execution: 87ms, fetching: 11ms)
Wykonanie zapytania trwało aż 87ms
. Skąd ta różnica?
SQL:
SELECT
"user_id", "robot", COUNT(*)
FROM
"sessions"
WHERE
"path" LIKE '/forum/%'
GROUP
BY "user_id", "robot"
DDL
create table sessions
(
id varchar(255) not null,
user_id integer,
robot varchar(255),
path varchar(1000)
)
;
Table "public.sessions"
Column | Type | Modifiers
---------+-------------------------+-----------
id | character varying(255) | not null
user_id | integer |
robot | character varying(255) |
path | character varying(1000) |
Indexes:
"sessions_path_index" btree (path text_pattern_ops)