Re: Planner very slow on same query to slightly different tables
От | Tom Lane |
---|---|
Тема | Re: Planner very slow on same query to slightly different tables |
Дата | |
Msg-id | 18183.1026956860@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Planner very slow on same query to slightly different tables (reina@nsi.edu (Tony Reina)) |
Ответы |
Re: Planner very slow on same query to slightly
|
Список | pgsql-hackers |
reina@nsi.edu (Tony Reina) writes: > db02=# explain select distinct area from center_out_cell where subject > = 'M' and arm = 'R' and rep = 10 and success = 1 and direction = 1; > NOTICE: QUERY PLAN: > Unique (cost=87795.47..87795.80 rows=13 width=5) > -> Sort (cost=87795.47..87795.47 rows=131 width=5) > -> Seq Scan on center_out_cell (cost=0.00..87790.87 rows=131 > width=5) > Index "pk1center_out_cell" > Column | Type > ------------+-------------- > subject | text > arm | character(1) > target | smallint > rep | integer > hemisphere | character(1) > area | text > filenumber | integer > dsp_chan | text > direction | smallint > unique btree > Index predicate: (success = 1) I imagine the problem with this index is that there's no constraint for "target" in the query; so the planner could only use the first two index columns (subject and arm), which probably isn't very selective. The index used in the other query is defined differently: > db02=# \d pk1circles_cell > Index "pk1circles_cell" > Column | Type > ------------+-------------- > subject | text > arm | character(1) > rep | integer > direction | smallint > hemisphere | character(1) > area | text > filenumber | integer > dsp_chan | text > unique btree > Index predicate: (success = 1) This allows "rep" to be used in the indexscan too (and if you were to cast properly, viz "direction = 1::smallint", then that column could be used as well). regards, tom lane
В списке pgsql-hackers по дате отправления: