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 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
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: 50
Planning time: 0.091 ms
Execution 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 returned to 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 difference is 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
--
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 returned to 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 msIn product no 'LIMIT 1' specified because first found result expected; in case of huge amout simple queries difference is 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