Re: [pgsql-de-allgemein] Query Planner wählt langsamen Bitmap Heap Scan statt Index Scan bei Limit

hallo …

das verhalten, das du siehst, ist ganz natürlich.
irgendwann sieht der optimizer, dass du immer mehr und mehr blöcke aus der tabelle benötigst. dem versucht er mit einem bitmap scan entgegen zu wirken, weil er annimmt, dass der index aufgrund von immer mehr random I/O für ihn geschätzt zu teuer wird.
real muss das nicht sein - kann aber sein (je nach caching, correlation on disk, etc.).
im prinzip gibts drei methoden:
a.) set enable_bitmapscan TO off … und hoffen, dass er beim index bleibt.
b.) set random_page_cost TO 1; … das macht random I/O relativ zur sequential I/O billiger und sollte ihn länger auf index scna halten
c.) versuch es mal mit einem sequential scan. wenn du genug daten löscht, kann das durchaus auch schneller sein

ein sequential scan ist bei solchen dingen oft gar nicht so schlecht wie sein ruf.
in summe ist es halt so, dass der planner die abschätzungen macht und entsprechend dem kostenmodell handelt.
das kostenmodell ist natürlich nur bedingt real.

liebe grüße,

hans

> On 16 Oct 2015, at 12:44, Robert J. Rotter <rotter(at)denic(dot)de> wrote:
>
> Hallo,
>
> ich habe ein kleines Problem:
> Ich habe eine Tabelle mit ca. 800 Mio. Zeilen auf die dauerhaft
> geschrieben und per primary key gelesen wird.
> Nun möchte ich ein Großteil der Zeilen aus der Tabelle löschen, Nämlich
> die, die in der Datumsspalte einen NULL Wert haben.
>
> Dazu habe ich ein Query geschrieben, das mir die Tabelle in kleinen Happen
> löschen soll,
> so das ich das im laufenden Betrieb tun kann.
>
> Also ein DELETE mit einer Subquery als IN-Kondition.
>
> Problem ist nun, wenn ich mit Limit einen gewissen Wert im Subquery
> überschreite, will der Planner einen
> Bitmap Heap Scan statt dem Index Scan durchführen, was dazu führt das die
> Abfrage nun mehrere Minuten benötigt,
> obwohl ich das Limit nur um eins erhöht habe.
>
> Leider habe ich bisher keine Abhilfe gefunden wie ich das Verhalten des
> Planers positiv beeinflussen kann.
>
> Hier sind die Query Pläne für die besagten Subquery:
>
> mydb=# explain select trx_id from myschema.identifier where mydate is null
> and col1=22083 order by trx_id limit 247;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------
> Limit (cost=0.70..135195.28 rows=247 width=21)
> -> Index Scan using pk_identifier on identifier (cost=0.70..2305971.48
> rows=4213 width=21)
> Index Cond: (col1 = 22083)
> Filter: (mydate IS NULL)
>
>
> mydb=# explain select trx_id from myschema.identifier where mydate is null
> and col1=22083 order by trx_id limit 248;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------
> Limit (cost=135273.21..135273.83 rows=248 width=21)
> -> Sort (cost=135273.21..135283.74 rows=4213 width=21)
> Sort Key: trx_id
> -> Bitmap Heap Scan on identifier (cost=118504.70..135084.59
> rows=4213 width=21)
> Recheck Cond: ((col1 = 22083) AND (mydate IS NULL))
> -> BitmapAnd (cost=118504.70..118504.70 rows=4213
> width=0)
> -> Bitmap Index Scan on pk_identifier
> (cost=0.00..41851.46 rows=842501 width=0)
> Index Cond: (col1 = 22083)
> -> Bitmap Index Scan on idx_mydate
> (cost=0.00..76650.89 rows=4150175 width=0)
> Index Cond: (mydate IS NULL)
>
> Die Tabelle hat einen kombinierten PK auf trx_id und col1 und einen
> Index auf der Datumspalte mydate.
>
> Kann mir jemand dabei helfen das Query zu beschleunigen, auch wenn ich das
> Limit erhöhe? Danke schonmal
>
>
> Viele Grüße
>
> Robert

-
Cybertec Schönig & Schönig GmbH
Professional PostgreSQL Support, Training, Consulting
www.postgresql-support.de <http://www.postgresql-support.de/>, www.cybertec.at <http://www.cybertec.at/>