Обсуждение: [BUGS] BUG #14750: Seq Scan instead of Index Scan works without limitation

The following bug has been logged on the website:

Bug reference:      14750
Logged by:          Alexey Markevich
Email address:      buhhunyx@tut.by
PostgreSQL version: 9.5.7
Operating system:   Windows X64
Description:

1) Create initial structure [1].

2) explain analyze SELECT * FROM accounts WHERE accountid = 'accountId50';
Index Scan using accounts_pkey on accounts  (cost=0.15..8.17 rows=1
width=272) (actual time=0.022..0.022 rows=1 loops=1)  Index Cond: ((accountid)::text = 'accountId50'::text)Planning
time:0.201 msExecution time: 0.045 ms
 

3) reindex table ACCOUNTS;

4) explain analyze SELECT * FROM accounts WHERE accountid = 'accountId50';
Seq Scan on accounts  (cost=0.00..2.50 rows=1 width=142) (actual
time=0.024..0.037 rows=1 loops=1)  Filter: ((accountid)::text = 'accountId50'::text)  Rows Removed by Filter:
119Planningtime: 0.095 msExecution time: 0.060 ms
 

Expected something like

explain analyze SELECT * FROM accounts WHERE accountid = 'accountId50' limit
1;
Limit  (cost=0.00..2.50 rows=1 width=142) (actual time=0.017..0.017 rows=1
loops=1)  ->  Seq Scan on accounts  (cost=0.00..2.50 rows=1 width=142) (actual
time=0.015..0.015 rows=1 loops=1)        Filter: ((accountid)::text = 'accountId50'::text)        Rows Removed by
Filter:50Planning time: 0.091 msExecution time: 0.033 ms
 

1. CREATE TABLE ACCOUNTS
(
ACCOUNTID VARCHAR(36) NOT NULL,
ACCOUNTNAME VARCHAR(200) NOT NULL,
ACCOUNTEMAIL VARCHAR (200),
PRIMARY KEY (ACCOUNTID),
UNIQUE (ACCOUNTNAME)
);

INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId00',
'accountId00');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId01',
'accountId01');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId02',
'accountId02');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId03',
'accountId03');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId04',
'accountId04');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId05',
'accountId05');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId06',
'accountId06');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId07',
'accountId07');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId08',
'accountId08');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId09',
'accountId09');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId10',
'accountId10');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId11',
'accountId11');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId12',
'accountId12');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId13',
'accountId13');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId14',
'accountId14');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId15',
'accountId15');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId16',
'accountId16');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId17',
'accountId17');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId18',
'accountId18');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId19',
'accountId19');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId20',
'accountId20');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId21',
'accountId21');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId22',
'accountId22');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId23',
'accountId23');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId24',
'accountId24');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId25',
'accountId25');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId26',
'accountId26');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId27',
'accountId27');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId28',
'accountId28');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId29',
'accountId29');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId30',
'accountId30');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId31',
'accountId31');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId32',
'accountId32');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId33',
'accountId33');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId34',
'accountId34');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId35',
'accountId35');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId36',
'accountId36');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId37',
'accountId37');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId38',
'accountId38');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId39',
'accountId39');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId40',
'accountId40');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId41',
'accountId41');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId42',
'accountId42');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId43',
'accountId43');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId44',
'accountId44');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId45',
'accountId45');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId46',
'accountId46');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId47',
'accountId47');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId48',
'accountId48');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId49',
'accountId49');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId50',
'accountId50');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId51',
'accountId51');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId52',
'accountId52');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId53',
'accountId53');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId54',
'accountId54');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId55',
'accountId55');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId56',
'accountId56');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId57',
'accountId57');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId58',
'accountId58');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId59',
'accountId59');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId60',
'accountId60');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId61',
'accountId61');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId62',
'accountId62');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId63',
'accountId63');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId64',
'accountId64');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId65',
'accountId65');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId66',
'accountId66');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId67',
'accountId67');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId68',
'accountId68');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId69',
'accountId69');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId70',
'accountId70');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId71',
'accountId71');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId72',
'accountId72');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId73',
'accountId73');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId74',
'accountId74');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId75',
'accountId75');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId76',
'accountId76');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId77',
'accountId77');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId78',
'accountId78');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId79',
'accountId79');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId80',
'accountId80');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId81',
'accountId81');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId82',
'accountId82');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId83',
'accountId83');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId84',
'accountId84');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId85',
'accountId85');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId86',
'accountId86');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId87',
'accountId87');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId88',
'accountId88');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId89',
'accountId89');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId90',
'accountId90');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId91',
'accountId91');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId92',
'accountId92');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId93',
'accountId93');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId94',
'accountId94');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId95',
'accountId95');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId96',
'accountId96');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId97',
'accountId97');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId98',
'accountId98');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId99',
'accountId99');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId100',
'accountId100');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId101',
'accountId101');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId102',
'accountId102');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId103',
'accountId103');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId104',
'accountId104');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId105',
'accountId105');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId106',
'accountId106');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId107',
'accountId107');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId108',
'accountId108');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId109',
'accountId109');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId110',
'accountId110');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId111',
'accountId111');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId112',
'accountId112');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId113',
'accountId113');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId114',
'accountId114');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId115',
'accountId115');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId116',
'accountId116');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId117',
'accountId117');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId118',
'accountId118');
INSERT INTO ACCOUNTS (accountid, accountname) VALUES ('accountId119',
'accountId119');



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

buhhunyx@tut.by writes:
> 1) Create initial structure [1].

> 2) explain analyze SELECT * FROM accounts WHERE accountid = 'accountId50';

>  Index Scan using accounts_pkey on accounts  (cost=0.15..8.17 rows=1
> width=272) (actual time=0.022..0.022 rows=1 loops=1)
>    Index Cond: ((accountid)::text = 'accountId50'::text)
>  Planning time: 0.201 ms
>  Execution time: 0.045 ms

> 3) reindex table ACCOUNTS;

> 4) explain analyze SELECT * FROM accounts WHERE accountid = 'accountId50';

>  Seq Scan on accounts  (cost=0.00..2.50 rows=1 width=142) (actual
> time=0.024..0.037 rows=1 loops=1)
>    Filter: ((accountid)::text = 'accountId50'::text)
>    Rows Removed by Filter: 119
>  Planning time: 0.095 ms
>  Execution time: 0.060 ms

I see no bug here.  The REINDEX caused the system's stats about the table
size to get updated, so the planner now realizes that the table is not
large enough to be worth bothering with an indexscan.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Seq Scan is fine except its ignore PRIMARY KEY and UNIQUE CONSTRAINT: in case result is found it should be immediatly
returnedto avoid scan rest of data:
 

# explain analyze select accountid from accounts where accountname = 'accountId50';Seq Scan on accounts
(cost=0.00..2.50rows=1 width=12) (actual time=0.018..0.029 rows=1 loops=1)  Filter: ((accountname)::text =
'accountId50'::text) Rows Removed by Filter: 119Planning time: 0.075 msExecution time: 0.048 ms
 

In product no 'LIMIT 1' specified because first found result expected; in case of huge amout simple queries difference
isvaluable:
 

# explain analyze select accountid from accounts where accountname = 'accountId50' limit 1;Limit  (cost=0.00..2.50
rows=1width=12) (actual time=0.016..0.016 rows=1 loops=1)  ->  Seq Scan on accounts  (cost=0.00..2.50 rows=1 width=12)
(actualtime=0.014..0.014 rows=1 loops=1)        Filter: ((accountname)::text = 'accountId50'::text)        Rows Removed
byFilter: 50Planning time: 0.084 msExecution time: 0.035 ms
 


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Thu, Jul 20, 2017 at 3:40 AM,  <buhhunyx@tut.by> wrote:
> Seq Scan is fine except its ignore PRIMARY KEY and UNIQUE CONSTRAINT: in case result is found it should be immediatly
returnedto avoid scan rest of data:
 
>
> # explain analyze select accountid from accounts where accountname = 'accountId50';
>  Seq Scan on accounts  (cost=0.00..2.50 rows=1 width=12) (actual time=0.018..0.029 rows=1 loops=1)
>    Filter: ((accountname)::text = 'accountId50'::text)
>    Rows Removed by Filter: 119
>  Planning time: 0.075 ms
>  Execution time: 0.048 ms
>
> In product no 'LIMIT 1' specified because first found result expected; in case of huge amout simple queries
differenceis valuable:
 
>
> # explain analyze select accountid from accounts where accountname = 'accountId50' limit 1;
>  Limit  (cost=0.00..2.50 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=1)
>    ->  Seq Scan on accounts  (cost=0.00..2.50 rows=1 width=12) (actual time=0.014..0.014 rows=1 loops=1)
>          Filter: ((accountname)::text = 'accountId50'::text)
>          Rows Removed by Filter: 50
>  Planning time: 0.084 ms
>  Execution time: 0.035 ms

I suppose that could be a valid query planner optimization, but it
wouldn't apply often.  The lack of that optimization isn't a bug, at
any rate.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs