PostgreSQL. Indexes and partitioning

Y

Yuri2012-05-03 11:38:59

PostgreSQL

Yuri, 2012-05-03 11:38:59

Good day, habrausers!

I came across a phenomenon incomprehensible to me in my favorite DBMS - PostgreSQL.

The point is this. There are two tables with the same data. The first is normal, the second is partitioned by an auto-incremental index (each partition contains 5 million records). The same index is the primary key in both tables. The problem is that queries related to sorting by this primary key in a partitioned table take an indecently long time. For example (the index is traditionally named id):
1) on a normal table:

explain analyze (select * from my_data order by id desc limit 10); QUERY PLAN ------------------------------------------------------------------------------------------------------ Limit (cost=0.00..7.26 rows=10 width=418) (actual time=0.012..26.831 rows=10 loops=1) -> Index Scan Backward using my_data_pkey on my_data (cost=0.00..20081200.21 rows=27676900 width=418) (actual time=0.010..26.809 rows=10 loops=1) Total runtime: 26.864 ms (3 rows) * This source code was highlighted with Source Code Highlighter.

2) on a partitioned table:

explain analyze (select * from my_data_master order by id desc limit 10); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=2189687.00..2189687.02 rows=10 width=100) (actual time=314882.294..314882.314 rows=10 loops=1) -> Sort (cost=2189687.00..2305298.08 rows=46244433 width=100) (actual time=314882.292..314882.301 rows=10 loops=1) Sort Key: public.my_data_master.id Sort Method: top-N heapsort Memory: 26kB -> Result (cost=0.00..1190361.43 rows=46244433 width=100) (actual time=209.347..250605.224 rows=46495464 loops=1) -> Append (cost=0.00..1190361.43 rows=46244433 width=100) (actual time=209.343..168812.754 rows=46495464 loops=1) -> Seq Scan on my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_5mln my_data_master (cost=0.00..0.00 rows=1 width=98) (actual time=0.002..0.002 rows=0 loops=1) -> Seq Scan on my_data_10mln my_data_master (cost=0.00..0.00 rows=1 width=99) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_15mln my_data_master (cost=0.00..0.00 rows=1 width=99) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_20mln my_data_master (cost=0.00..0.00 rows=1 width=98) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_25mln my_data_master (cost=0.00..0.00 rows=1 width=99) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_30mln my_data_master (cost=0.00..81068.66 rows=1934658 width=100) (actual time=209.329..7484.434 rows=1934658 loops=1) -> Seq Scan on my_data_35mln my_data_master (cost=0.00..85868.90 rows=126902 width=100) (actual time=9.837..7667.541 rows=247612 loops=1) -> Seq Scan on my_data_40mln my_data_master (cost=0.00..85877.48 rows=54482 width=100) (actual time=11.843..8542.494 rows=107813 loops=1) -> Seq Scan on my_data_45mln my_data_master (cost=0.00..85970.71 rows=82715 width=101) (actual time=16.713..8438.728 rows=162790 loops=1) -> Seq Scan on my_data_50mln my_data_master (cost=0.00..114586.52 rows=3532521 width=100) (actual time=12.295..9758.697 rows=3532521 loops=1) -> Seq Scan on my_data_55mln my_data_master (cost=0.00..90983.00 rows=5000000 width=100) (actual time=19.895..6268.184 rows=5000000 loops=1) -> Seq Scan on my_data_60mln my_data_master (cost=0.00..90966.00 rows=5000000 width=100) (actual time=14.538..6459.220 rows=5000000 loops=1) -> Seq Scan on my_data_65mln my_data_master (cost=0.00..90746.00 rows=5000000 width=100) (actual time=9.685..6464.820 rows=5000000 loops=1) -> Seq Scan on my_data_70mln my_data_master (cost=0.00..90985.00 rows=5000000 width=100) (actual time=11.304..6468.359 rows=5000000 loops=1) -> Seq Scan on my_data_75mln my_data_master (cost=0.00..90958.00 rows=5000000 width=100) (actual time=17.406..6389.408 rows=5000000 loops=1) -> Seq Scan on my_data_80mln my_data_master (cost=0.00..90970.00 rows=5000000 width=99) (actual time=13.072..6537.780 rows=5000000 loops=1) -> Seq Scan on my_data_85mln my_data_master (cost=0.00..90950.00 rows=5000000 width=99) (actual time=8.716..6582.697 rows=5000000 loops=1) -> Seq Scan on my_data_90mln my_data_master (cost=0.00..90934.00 rows=5000000 width=99) (actual time=11.954..6799.891 rows=5000000 loops=1) -> Seq Scan on my_data_95mln my_data_master (cost=0.00..9274.07 rows=510070 width=100) (actual time=7.106..640.611 rows=510070 loops=1) -> Seq Scan on my_data_100mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.003..0.003 rows=0 loops=1) -> Seq Scan on my_data_105mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_110mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_115mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_120mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.000..0.000 rows=0 loops=1) -> Seq Scan on my_data_125mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_130mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_135mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_140mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_145mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_150mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_155mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_160mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_165mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_170mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_175mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_180mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_185mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_190mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_195mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) -> Seq Scan on my_data_200mln my_data_master (cost=0.00..10.14 rows=140 width=532) (actual time=0.001..0.001 rows=0 loops=1) Total runtime: 314882.697 ms (48 rows) * This source code was highlighted with Source Code Highlighter.

Same story with a query like:

select max(id) from my_data_master; * This source code was highlighted with Source Code Highlighter.

Prompt, guru, is there any trick in indexes for partitioned tables?

Reply

Answer the question

In order to leave comments, you need to log in

7 answer(s)

G

galaxy, 2012-05-12
@bRUtality

Hmm… do you have exclude checks on child tables (i.e. alter table cdr_data_90mln add constraint cc_cdr_data_90mln check (id >= 90000000 and id < 95000000) )?

K

Konstantin, 2012-05-03
@Norraxx

I would say that there are no indexes there, otherwise there would be no Seq Scan. Or indexes are not included.
"For each partition, create an index on the key column(s), as well as any other indexes you might want. (The key index is not strictly necessary, but in most scenarios it is helpful. If you intend the key values to be unique then you should always create a unique or primary-key constraint for each partition.)"
www.postgresql.org/ docs/9.1/static/ddl-partitioning.html

P

Pavel Nazarov, 2012-05-03
@smbd

1. Check the following parameter: 2. Check the query with an explicit filter on the partitioned field (WHERE id > ...). 3. If it is OK, then most likely PostgreSQL will run through all partitions for the queries you provided, since it is not clear to it which one to access. It is recommended to make explicit requests to the desired partition, or filter by . Always yours :)
SHOW constraint_exclusion ; constraint_exclusion ---------------------- partition

P

Pavel Nazarov, 2012-05-03
@smbd

Ahh, parser.
>> filter by id

G

galaxy, 2012-05-03
@galaxy

What version of postgres do you have? archives.postgresql.org/pgsql-performance/2011-04/msg00385.php
See also what happens with enable_seqscan=off. Maybe he lacks statistics on the tables?

K

Konstantin, 2012-05-03
@Norraxx

All the same, I'm not special in such things, but try to check if the indexes are enabled.
ALTER TABLE cdr_data_100mln ENABLE TRIGGER cdr_data_100mln_pkey;
IMHO I would do indexes as hash. Because B-TREE is used for searching by words/long values and HASH for exact matching. CREATE INDEX cdr_data_100mln_hash_idx ON cdr_data_100mln USING hash;

Y

Yuri, 2012-05-05
@bRUtality

Hello colleagues!
Updated to 9.1.3. Work became twice as fast. But still, not satisfactory.
A query on indexes from a topic now takes 0.5 seconds and 150 seconds, respectively. Need to dig further. But I don't know where :(