I have a Postgres database with a partitioned table that will contain ~2,000,000,000 entries.
I have defined a partitioned database based on the first letter of the "identifier" - this is split into 37 sub-tables, [0-9, a-z, default (catchall for everything else)].
The database is very simple and straight forward, and is defined below.
create table entries (
id bigserial,
identifier text null,
password text null,
additional_fields jsonb
)
partition by list (lower(left(identifier, 1)));
ALTER DATABASE credentials SET constraint_exclusion=on;
CREATE TABLE entries_0 PARTITION OF entries for values in ('0');
CREATE TABLE entries_1 PARTITION OF entries for values in ('1');
CREATE TABLE entries_2 PARTITION OF entries for values in ('2');
CREATE TABLE entries_3 PARTITION OF entries for values in ('3');
...
CREATE TABLE entries_z PARTITION OF entries for values in ('z');
ALTER TABLE entries_0 ADD CONSTRAINT first_letter CHECK (lower(left(identifier, 1)) = '0');
ALTER TABLE entries_1 ADD CONSTRAINT first_letter CHECK (lower(left(identifier, 1)) = '1');
ALTER TABLE entries_2 ADD CONSTRAINT first_letter CHECK (lower(left(identifier, 1)) = '2');
ALTER TABLE entries_3 ADD CONSTRAINT first_letter CHECK (lower(left(identifier, 1)) = '3');
...
ALTER TABLE entries_z ADD CONSTRAINT first_letter CHECK (lower(left(identifier, 1)) = 'z');
CREATE INDEX ident_idx on entries(identifier);
However, when I run an EXPLAIN
it says that is it is still doing a sequential scan.
EXPLAIN SELECT * FROM entries where identifier = 'some_identifier_from_subtable_s' LIMIT 1;
Output:
Limit (cost=0.00..140.92 rows=1 width=104)
-> Append (cost=0.00..43418531.72 rows=308113 width=104)
-> Seq Scan on entries_0 (cost=0.00..23239.38 rows=2 width=68)
Filter: (identifier = 'some_identifier_from_subtable_s'::text)
-> Seq Scan on entries_1 (cost=0.00..150187.81 rows=6 width=68)
Filter: (identifier = 'some_identifier_from_subtable_s'::text)
-> Seq Scan on entries_2 (cost=0.00..94694.38 rows=4 width=67)
Filter: (identifier = 'some_identifier_from_subtable_s'::text)
-> Seq Scan on entries_3 (cost=0.00..81656.71 rows=3 width=67)
Filter: (identifier = 'some_identifier_from_subtable_s'::text)
... etc.
-> Seq Scan on entries_z (cost=0.00..579207.95 rows=13 width=69)
Filter: (identifier = 'some_identifier_from_subtable_s'::text)
-> Seq Scan on entries_default (cost=0.00..15582.36 rows=4 width=69)
Filter: (identifier = 'some_identifier_from_subtable_s'::text)
What am I doing incorrectly? The intelligent partitioning should be able to redirect the query to just the entries_s
partition, should it not? And then the CREATE INDEX ident_idx on entries(identifier);
should make the query go through the index?
After adding the explicit query the new plan looks like this:
FROM entries
WHERE identifier = 'some_identifier_from_subtable_s'
AND lower(left(identifier, 1)) = 's' -- 1st letter of above identifier
LIMIT 1;
Output:
Limit (cost=1000.00..2583053.76 rows=1 width=71)
-> Gather (cost=1000.00..2583053.76 rows=1 width=71)
Workers Planned: 2
-> Parallel Append (cost=0.00..2582053.66 rows=1 width=71)
-> Parallel Seq Scan on entries_s (cost=0.00..2582053.66 rows=1 width=71)
Filter: ((identifier = 'some_identifier_from_subtable_s'::text) AND (lower("left"(identifier, 1)) = 's'::text))
It's still doing a sequential scan on entries_s
. Does declaring the index with CREATE INDEX ident_idx on entries(identifier);
not propagate it down to all partitions?
where lower(left(identifier,1)) = 's' and identifier = 'some_identifier_from_subtable_s' LIMIT 1;
– a_horse_with_no_name Mar 28 '19 at 15:59