Mantenere PostgreSQL a volte scegliendo un piano di query non valido

Ho uno strano problema con le prestazioni di PostgreSQL per una query, usando PostgreSQL 8.4.9. Questa query sta selezionando un set di punti all’interno di un volume 3D, utilizzando un LEFT OUTER JOIN per aggiungere una colonna ID correlata in cui esiste l’ID correlato. Piccole variazioni nell’intervallo di x possono far sì che PostgreSQL scelga un piano di query diverso, che impiega il tempo di esecuzione da 0,01 secondi a 50 secondi. Questa è la query in questione:

 SELECT treenode.id AS id, treenode.parent_id AS parentid, (treenode.location).x AS x, (treenode.location).y AS y, (treenode.location).z AS z, treenode.confidence AS confidence, treenode.user_id AS user_id, treenode.radius AS radius, ((treenode.location).z - 50) AS z_diff, treenode_class_instance.class_instance_id AS skeleton_id FROM treenode LEFT OUTER JOIN (treenode_class_instance INNER JOIN class_instance ON treenode_class_instance.class_instance_id = class_instance.id AND class_instance.class_id = 7828307) ON (treenode_class_instance.treenode_id = treenode.id AND treenode_class_instance.relation_id = 7828321) WHERE treenode.project_id = 4 AND (treenode.location).x >= 8000 AND (treenode.location).x = 22244 AND (treenode.location).y = 0 AND (treenode.location).z <= 100 ORDER BY parentid DESC, id, z_diff LIMIT 400; 

Quella query richiede quasi un minuto e, se aggiungo EXPLAIN all’inizio di quella query, sembra che stia utilizzando il seguente piano di query:

  Limit (cost=56185.16..56185.17 rows=1 width=89) -> Sort (cost=56185.16..56185.17 rows=1 width=89) Sort Key: treenode.parent_id, treenode.id, (((treenode.location).z - 50::double precision)) -> Nested Loop Left Join (cost=6715.16..56185.15 rows=1 width=89) Join Filter: (treenode_class_instance.treenode_id = treenode.id) -> Bitmap Heap Scan on treenode (cost=148.55..184.16 rows=1 width=81) Recheck Cond: (((location).x >= 8000::double precision) AND ((location).x = 0::double precision) AND ((location).z = 22244::double precision) AND ((location).y  BitmapAnd (cost=148.55..148.55 rows=9 width=0) -> Bitmap Index Scan on location_x_index (cost=0.00..67.38 rows=2700 width=0) Index Cond: (((location).x >= 8000::double precision) AND ((location).x  Bitmap Index Scan on location_z_index (cost=0.00..80.91 rows=3253 width=0) Index Cond: (((location).z >= 0::double precision) AND ((location).z  Hash Join (cost=6566.61..53361.69 rows=211144 width=16) Hash Cond: (treenode_class_instance.class_instance_id = class_instance.id) -> Seq Scan on treenode_class_instance (cost=0.00..25323.79 rows=969285 width=16) Filter: (relation_id = 7828321) -> Hash (cost=5723.54..5723.54 rows=51366 width=8) -> Seq Scan on class_instance (cost=0.00..5723.54 rows=51366 width=8) Filter: (class_id = 7828307) (20 rows) 

Tuttavia, se sostituisco l’ 8000 nella condizione x range con 10644 , la query viene eseguita in una frazione di secondo e utilizza questo piano di query:

  Limit (cost=58378.94..58378.95 rows=2 width=89) -> Sort (cost=58378.94..58378.95 rows=2 width=89) Sort Key: treenode.parent_id, treenode.id, (((treenode.location).z - 50::double precision)) -> Hash Left Join (cost=57263.11..58378.93 rows=2 width=89) Hash Cond: (treenode.id = treenode_class_instance.treenode_id) -> Bitmap Heap Scan on treenode (cost=231.12..313.44 rows=2 width=81) Recheck Cond: (((location).z >= 0::double precision) AND ((location).z = 10644::double precision) AND ((location).x = 22244::double precision) AND ((location).y  BitmapAnd (cost=231.12..231.12 rows=21 width=0) -> Bitmap Index Scan on location_z_index (cost=0.00..80.91 rows=3253 width=0) Index Cond: (((location).z >= 0::double precision) AND ((location).z  Bitmap Index Scan on location_x_index (cost=0.00..149.95 rows=6157 width=0) Index Cond: (((location).x >= 10644::double precision) AND ((location).x  Hash (cost=53361.69..53361.69 rows=211144 width=16) -> Hash Join (cost=6566.61..53361.69 rows=211144 width=16) Hash Cond: (treenode_class_instance.class_instance_id = class_instance.id) -> Seq Scan on treenode_class_instance (cost=0.00..25323.79 rows=969285 width=16) Filter: (relation_id = 7828321) -> Hash (cost=5723.54..5723.54 rows=51366 width=8) -> Seq Scan on class_instance (cost=0.00..5723.54 rows=51366 width=8) Filter: (class_id = 7828307) (21 rows) 

Sono lontano da un esperto nell’analisi di questi piani di query, ma la chiara differenza sembra essere che con un intervallo x utilizza un Hash Left Join per LEFT OUTER JOIN (che è molto veloce), mentre con l’altra gamma utilizza un Nested Loop Left Join (che sembra essere molto lento). In entrambi i casi le query restituiscono circa 90 righe. Se SET ENABLE_NESTLOOP TO FALSE prima della versione lenta della query, è molto veloce, ma capisco che l’ utilizzo di tale impostazione in generale sia una ctriggers idea .

Posso, ad esempio, creare un indice particolare per rendere più probabile che il pianificatore di query sceglierà la strategia chiaramente più efficiente? Qualcuno potrebbe suggerire perché il pianificatore di query di PostgreSQL dovrebbe scegliere una strategia così scarsa per una di queste query? Di seguito ho incluso i dettagli dello schema che possono essere utili.


    La tabella treenode ha 900.000 righe ed è definita come segue:

      Table "public.treenode" Column | Type | Modifiers ---------------+--------------------------+------------------------------------------------------ id | bigint | not null default nextval('concept_id_seq'::regclass) user_id | bigint | not null creation_time | timestamp with time zone | not null default now() edition_time | timestamp with time zone | not null default now() project_id | bigint | not null location | double3d | not null parent_id | bigint | radius | double precision | not null default 0 confidence | integer | not null default 5 Indexes: "treenode_pkey" PRIMARY KEY, btree (id) "treenode_id_key" UNIQUE, btree (id) "location_x_index" btree (((location).x)) "location_y_index" btree (((location).y)) "location_z_index" btree (((location).z)) Foreign-key constraints: "treenode_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES treenode(id) Referenced by: TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_treenode_id_fkey" FOREIGN KEY (treenode_id) REFERENCES treenode(id) ON DELETE CASCADE TABLE "treenode" CONSTRAINT "treenode_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES treenode(id) Triggers: on_edit_treenode BEFORE UPDATE ON treenode FOR EACH ROW EXECUTE PROCEDURE on_edit() Inherits: location 

    Il tipo di composito double3d è definito come segue:

     Composite type "public.double3d" Column | Type --------+------------------ x | double precision y | double precision z | double precision 

    Le altre due tabelle coinvolte nel join sono treenode_class_instance :

      Table "public.treenode_class_instance" Column | Type | Modifiers -------------------+--------------------------+------------------------------------------------------ id | bigint | not null default nextval('concept_id_seq'::regclass) user_id | bigint | not null creation_time | timestamp with time zone | not null default now() edition_time | timestamp with time zone | not null default now() project_id | bigint | not null relation_id | bigint | not null treenode_id | bigint | not null class_instance_id | bigint | not null Indexes: "treenode_class_instance_pkey" PRIMARY KEY, btree (id) "treenode_class_instance_id_key" UNIQUE, btree (id) "idx_class_instance_id" btree (class_instance_id) Foreign-key constraints: "treenode_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE "treenode_class_instance_relation_id_fkey" FOREIGN KEY (relation_id) REFERENCES relation(id) "treenode_class_instance_treenode_id_fkey" FOREIGN KEY (treenode_id) REFERENCES treenode(id) ON DELETE CASCADE "treenode_class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Triggers: on_edit_treenode_class_instance BEFORE UPDATE ON treenode_class_instance FOR EACH ROW EXECUTE PROCEDURE on_edit() Inherits: relation_instance 

    … e class_instance :

      Table "public.class_instance" Column | Type | Modifiers ---------------+--------------------------+------------------------------------------------------ id | bigint | not null default nextval('concept_id_seq'::regclass) user_id | bigint | not null creation_time | timestamp with time zone | not null default now() edition_time | timestamp with time zone | not null default now() project_id | bigint | not null class_id | bigint | not null name | character varying(255) | not null Indexes: "class_instance_pkey" PRIMARY KEY, btree (id) "class_instance_id_key" UNIQUE, btree (id) Foreign-key constraints: "class_instance_class_id_fkey" FOREIGN KEY (class_id) REFERENCES class(id) "class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Referenced by: TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_a_fkey" FOREIGN KEY (class_instance_a) REFERENCES class_instance(id) ON DELETE CASCADE TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_b_fkey" FOREIGN KEY (class_instance_b) REFERENCES class_instance(id) ON DELETE CASCADE TABLE "connector_class_instance" CONSTRAINT "connector_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE Triggers: on_edit_class_instance BEFORE UPDATE ON class_instance FOR EACH ROW EXECUTE PROCEDURE on_edit() Inherits: concept 

    Se il pianificatore di query prende decisioni sbagliate, è principalmente una delle due cose:

    1. Le statistiche sono distriggerste.

    Significato “impreciso”, non “distriggersto”.

    Hai eseguito ANALYZE abbastanza? Popolare anche nella sua forma combinata VACUUM ANALYZE . Se autovacuum è attivo (che è l’impostazione predefinita in Postgres dei giorni nostri), ANALYZE viene eseguito automaticamente. Ma considera:

    • Sono ancora raccomandati VACUUM ANALYZE regolari sotto 9.1?

    (Le prime due risposte si applicano ancora a Postgres 9.6.)

    Se la tua tabella è grande e la distribuzione dei dati è irregolare , può essere utile aumentare il valore default_statistics_target . O meglio, imposta semplicemente il target delle statistiche per le colonne rilevanti (quelle in WHERE o JOIN delle tue query, in pratica):

     ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1234; -- calibrate number 

    L’objective può essere impostato nell’intervallo da 0 a 10000;

    Esegui ANALYZE nuovo dopo quello (sulle tabelle pertinenti).

    2. Le impostazioni di costo per le stime del planner sono distriggerste.

    Leggere il capitolo Costo costanti del pianificatore nel manuale.

    Osserva i capitoli default_statistics_target e random_page_cost su questa pagina Wiki PostgreSQL generalmente utile .

    Naturalmente, ci possono essere molte altre possibili ragioni, ma queste sono di gran lunga le più comuni.

    Sono scettico sul fatto che ciò abbia a che fare con statistiche errate a meno che non si consideri la combinazione delle statistiche del database e del tipo di dati personalizzato.

    La mia ipotesi è che PostgreSQL stia selezionando un join loop annidato perché guarda i predicati (treenode.location).x >= 8000 AND (treenode.location).x <= (8000 + 4736) e fa qualcosa di funky nell'aritmetica di il tuo paragone Un ciclo annidato viene in genere utilizzato quando si dispone di una piccola quantità di dati nella parte interna del join.

    Ma, una volta che si imposta la costante su 10736, si ottiene un piano diverso. È sempre ansible che il piano sia sufficientemente complesso che la Genetic Query Optimization (GEQO) sta dando il via e stai vedendo gli effetti collaterali della costruzione di piani non deterministici . Ci sono abbastanza discrepanze nell'ordine di valutazione nelle domande per farmi pensare che è quello che sta succedendo.

    Un'opzione consisterebbe nell'esaminare usando una dichiarazione parametrizzata / preparata per questo invece di usare un codice ad hoc. Poiché stai lavorando in uno spazio tridimensionale, potresti anche voler considerare l'utilizzo di PostGIS . Anche se potrebbe essere eccessivo, potrebbe anche essere in grado di fornirti le prestazioni necessarie per far funzionare correttamente queste query.

    Mentre forzare il comportamento del pianificatore non è la scelta migliore, a volte finiamo per prendere decisioni migliori rispetto al software.

    Cosa ha detto Erwin sulle statistiche. Anche:

     ORDER BY parentid DESC, id, z_diff 

    Ordinamento

     parentid DESC, id, z 

    potrebbe dare all’ottimizzatore un po ‘più di spazio per mescolare. (Non penso che sarà molto importante dal momento che è l’ultimo termine, e il tipo non è così costoso, ma si potrebbe fare un tentativo)

    Non sono sicuro che sia la fonte del tuo problema, ma sembra che siano state apportate alcune modifiche nel pianificatore di query postgres tra le versioni 8.4.8 e 8.4.9. Potresti provare a utilizzare una versione precedente e vedere se fa la differenza.

    http://postgresql.1045698.n5.nabble.com/BUG-6275-Horrible-performance-regression-td4944891.html

    Non dimenticare di rianalizzare le tue tabelle se cambi la versione.