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)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
