Hi,
I've been working on trying to partition a big table (I've never partitioned a
table in any other database till now).
Everything went ok, except one query that didn't work afterwards.
I've put the partition description, indexes, etc ..., and the explain plan
attached.
The query is extremely fast without partition (index scan backards on the
primary key)
The query is : "select * from logs order by id desc limit 100;"
id is the primary key.
It is indexed on all partitions.
But the explain plan does full table scan on all partitions.
While I think I understand why it is doing this plan right now, is there
something that could be done to optimize this case ? Or put a warning in the
docs about this kind of behaviour. I guess normally someone would partition
to get faster queries :)
Anyway, I thought I should mention this, as it has been quite a surprise.
CREATE TABLE logs_150 (CHECK ( id_machine = 150)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_163 (CHECK ( id_machine = 163)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_289 (CHECK ( id_machine = 289)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_319 (CHECK ( id_machine = 319)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_238 (CHECK ( id_machine = 238)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_148 (CHECK ( id_machine = 148)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_176 (CHECK ( id_machine = 176)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_164 (CHECK ( id_machine = 164)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_316 (CHECK ( id_machine = 316)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_313 (CHECK ( id_machine = 313)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_217 (CHECK ( id_machine = 217)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_167 (CHECK ( id_machine = 167)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_287 (CHECK ( id_machine = 287)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_279 (CHECK ( id_machine = 279)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_248 (CHECK ( id_machine = 248)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_280 (CHECK ( id_machine = 280)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_165 (CHECK ( id_machine = 165)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_330 (CHECK ( id_machine = 330)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_149 (CHECK ( id_machine = 149)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_157 (CHECK ( id_machine = 157)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_272 (CHECK ( id_machine = 272)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_311 (CHECK ( id_machine = 311)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_309 (CHECK ( id_machine = 309)) INHERITS (logs) TABLESPACE data_logs;
CREATE TABLE logs_318 (CHECK ( id_machine = 318)) INHERITS (logs) TABLESPACE data_logs;
CREATE UNIQUE INDEX logs_150_pkey ON logs_150 (id) TABLESPACE index_logs;CREATE INDEX date_150 ON logs_150 (date) TABLESPACE index_logs ;CREATE INDEX event_150 ON logs_150 (evenement) TABLESPACE index_logs;
.....
logs=> explain select * from logs order by id desc limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------
Limit (cost=16524647.29..16524647.54 rows=100 width=295)
-> Sort (cost=16524647.29..16568367.11 rows=17487927 width=295)
Sort Key: public.logs.id
-> Result (cost=0.00..827622.27 rows=17487927 width=295)
-> Append (cost=0.00..827622.27 rows=17487927 width=295)
-> Seq Scan on logs (cost=0.00..826232.78 rows=17451978 width=165)
-> Seq Scan on logs_150 logs (cost=0.00..199.04 rows=6104 width=144)
-> Seq Scan on logs_163 logs (cost=0.00..261.79 rows=7079 width=169)
-> Seq Scan on logs_289 logs (cost=0.00..428.93 rows=10693 width=200)
-> Seq Scan on logs_319 logs (cost=0.00..31.92 rows=992 width=129)
-> Seq Scan on logs_238 logs (cost=0.00..28.01 rows=701 width=199)
-> Seq Scan on logs_148 logs (cost=0.00..80.15 rows=2015 width=195)
-> Seq Scan on logs_176 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_164 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_316 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_313 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_217 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_167 logs (cost=0.00..57.36 rows=1536 width=170)
-> Seq Scan on logs_287 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_279 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_248 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_280 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_165 logs (cost=0.00..34.09 rows=909 width=168)
-> Seq Scan on logs_330 logs (cost=0.00..62.46 rows=1746 width=160)
-> Seq Scan on logs_149 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_157 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_272 logs (cost=0.00..32.14 rows=814 width=183)
-> Seq Scan on logs_311 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_309 logs (cost=0.00..12.40 rows=240 width=295)
-> Seq Scan on logs_318 logs (cost=0.00..12.40 rows=240 width=295)
