From: | armand pirvu <armand(dot)pirvu(at)gmail(dot)com> |
---|---|
To: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | limit and query planner |
Date: | 2018-06-05 18:24:25 |
Message-ID: | 59ED87E7-B582-481A-9FD8-CB77C7A3B268@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
All
Please see below
explain analyze select * from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
and status=0 limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.29 rows=10 width=519) (actual time=0.110..0.439 rows=10 loops=1)
-> Seq Scan on sp_i2birst_reg_staging_test (cost=0.00..548.40 rows=4239 width=519) (actual time=0.109..0.429 rows=10 loops=1)
Filter: (((evt_id)::text = 'ACSF17'::text) AND (status = 0))
Rows Removed by Filter: 115
Planning time: 3.022 ms
Execution time: 0.639 ms
(6 rows)
birstdb=# \d sp_i2birst_reg_staging_test
Table "csischema.sp_i2birst_reg_staging_test"
Column | Type | Modifiers
---------------+-----------------------------+-------------------------------------------------------------------------
action_id | bigint | not null default nextval('i2birst_reg_staging_action_id_seq'::regclass)
reg_uid | integer | not null
evt_uid | integer | not null
evt_id | character varying(10) | not null
operation | character varying(6) | not null
status | smallint | not null
category | character varying(20) | not null default ''::character varying
add_date | timestamp with time zone | not null default now()
mod_date | timestamp with time zone | not null default now()
ingres_data | jsonb |
thread_number | bigint | not null default 0
start_time | timestamp without time zone |
end_time | timestamp without time zone |
Indexes:
"sp_i2birst_reg_staging_test_pkey" PRIMARY KEY, btree (action_id)
"sp_i2birst_reg_staging_test_idx" btree (status, evt_id, category)
Check constraints:
"sp_i2birst_reg_staging_test_status_check" CHECK (status = ANY (ARRAY[0, 1, 2, 3]))
Even if add an index on evt_id and status same table scan
But
select count(*) from sp_i2birst_reg_staging_test;
count
-------
6860
select count(*) from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
and status=0 ;
count
-------
4239
So I can see why the planner is choosing a table scan
My question is: I suspect the limit simply limits the fethching to the first n-records retrieved and has no implications whatsoever on the planner, meaning the planner ignores it. Am I right or wrong ?
Thanks
— Armand
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2018-06-05 18:32:22 | Re: limit and query planner |
Previous Message | Peter Geoghegan | 2018-06-05 18:03:38 | Re: Code of Conduct plan |