Re: limit and query planner

[armand pirvu <armand.pirvu@xxxxxxxxx>]

On Jun 5, 2018, at 1:32 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:

2018-06-05 20:24 GMT+02:00 armand pirvu <armand.pirvu@xxxxxxxxx>:

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 ?

LIMIT is last clause and it is processed after aggregation.

probably you would select count(*) from (select * from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'  LIMIT 10) s;

more you have not index on evt_id column - there is composite index, but the chance can be low

Regards

Pavel

Thanks
— Armand

Thank you Pavel

I put the counts to show that the number of records retrieved without limit relative to a plain select count(*) is far more than 5% and an index is just from this very reason deemed useless, aka the restriction is really non existent

— Armand