Hoe dwing ik Postgres om een ​​bepaalde index te gebruiken?

Hoe dwing ik Postgres om een ​​index te gebruiken als het anders zou aandringen op een sequentiële scan?


Antwoord 1, autoriteit 100%

Ervan uitgaande dat u vraagt ​​naar de algemene “index hinting”-functie die in veel databases wordt aangetroffen, biedt PostgreSQL zo’n functie niet. Dit was een bewuste beslissing van het PostgreSQL-team. Een goed overzicht van waarom en wat u in plaats daarvan kunt doen, vindt u hier. De redenen zijn eigenlijk dat het een prestatiehack is die later meer problemen veroorzaakt als uw gegevens veranderen, terwijl de optimalisatieprogramma van PostgreSQL het plan opnieuw kan evalueren op basis van de statistieken. Met andere woorden, wat vandaag de dag een goed zoekplan is, is waarschijnlijk niet voor altijd een goed zoekplan, en indexhints dwingen een bepaald zoekplan voor altijd af.

Als zeer botte hamer, handig om te testen, kun je de parameters enable_seqscanen enable_indexscangebruiken. Zie:

Deze zijn niet geschikt voor continu productiegebruik. Als je problemen hebt met het kiezen van een queryplan, zou je de documentatie voor het opsporen van prestatieproblemen opvragen. Stel niet zomaar enable_parameters in en loop weg.

Tenzij u een zeer goede reden heeft om de index te gebruiken, maakt Postgres mogelijk de juiste keuze. Waarom?

  • Voor kleine tabellen is het sneller om opeenvolgende scans uit te voeren.
  • Postgres gebruikt geen indexen wanneer gegevenstypes niet goed overeenkomen, het kan zijn dat u de juiste casts moet opnemen.
  • Uw planner-instellingen kunnen problemen veroorzaken.

Zie ook dit oude nieuwsgroepbericht.


Antwoord 2, autoriteit 78%

Waarschijnlijk de enige geldige reden voor het gebruik

set enable_seqscan=false

is wanneer u query’s schrijft en snel wilt zien wat het queryplan daadwerkelijk zou zijn als er grote hoeveelheden gegevens in de tabel(len) zouden staan. Of natuurlijk als u snel moet bevestigen dat uw zoekopdracht geen index gebruikt, simpelweg omdat de dataset te klein is.


Antwoord 3, autoriteit 29%

TL;DR

Voer de volgende drie opdrachten uit en controleer of het probleem is opgelost:

ANALYZE;
SET random_page_cost = 1.0;
SET effective_cache_size = 'X GB';    # replace X with total RAM size minus 2 GB

Lees verder voor meer details en achtergrondinformatie hierover.

Stap 1: Analyseer tabellen

Als een eenvoudige eerste poging om het probleem op te lossen, voert u de opdracht ANALYZE;uit als de database-supergebruiker om alle tabelstatistieken bij te werken. Uit de documentatie:

De queryplanner gebruikt deze statistieken om de meest efficiënte uitvoeringsplannen voor query’s te helpen bepalen.

Stap 2: Stel de juiste willekeurige paginakosten in

Voor indexscans zijn niet-sequentiële schijfpagina’s nodig. PostgreSQL gebruikt de configuratieparameter random_page_costom de kosten van dergelijke niet-sequentiële ophaalacties te schatten in verhouding tot opeenvolgende ophaalacties. Uit de documentatie:

Als u deze waarde verlaagt […] zal het systeem de voorkeur geven aan indexscans; het verhogen ervan zal indexscans er relatief duurder uit laten zien.

De standaardwaarde is 4.0, waarbij wordt uitgegaan van een gemiddeldekostenfactor van 4 in vergelijking met sequentiële ophaalacties, waarbij rekening wordt gehouden met caching-effecten. Als uw database echter is opgeslagen op een SSD-schijf, moet u eigenlijk random_page_costinstellen op 1.1volgens de documentatie:

Opslag die lage willekeurige leeskosten heeft in vergelijking met sequentiële, bijv. solid-state schijven, kan ook beter worden gemodelleerd met een lagere waarde voor random_page_cost, bijv. 1.1.

Als een index grotendeels (of zelfs volledig) in het RAM-geheugen is opgeslagen, zal een indexscan altijd aanzienlijksneller zijn dan een op schijf bediende sequentiële scan. De queryplanner weet echter niet welke delen van de index al in de cache zijn opgeslagen en kan dus een verkeerde beslissing nemen.

Als uw database-indexen vaak worden gebruikt en als het systeem voldoende RAM heeft, zullen de indices uiteindelijk waarschijnlijk in de cache worden opgeslagen. In een dergelijk geval kan random_page_costworden ingesteld op 1.0, of zelfs op een waarde lager dan 1.0om agressief de voorkeur te geven aan het gebruik van indexscans (hoewel de documentatie raadt het af om dat te doen). Je zult met verschillende waarden moeten experimenteren en kijken wat voor jou werkt.

Als kanttekening kun je ook overwegen om de pg_prewarmextensie om uw indexen expliciet in het RAM-geheugen op te slaan.

U kunt de random_page_costals volgt instellen:

SET random_page_cost = 1.0;

Stap 3: Stel de juiste cachegrootte in

Op een systeem met 8 of meer GB RAM moet u de configuratieparameter effective_cache_sizeinstellen op de hoeveelheid geheugen die normaal gesproken beschikbaar is voor PostgreSQL voor gegevenscaching. Uit de documentatie:

Een hogere waarde maakt het waarschijnlijker dat indexscans worden gebruikt, een lagere waarde maakt het waarschijnlijker dat opeenvolgende scans worden gebruikt.

Houd er rekening mee dat deze parameter de hoeveelheid geheugen die PostgreSQL daadwerkelijk zal toewijzen niet verandert, maar alleen wordt gebruikt om kostenramingen te berekenen. Een redelijke waarde (tenminste op een speciale databaseserver) is de totale RAM-grootte minus 2 GB. De standaardwaarde is 4 GB.

Je kunt de effective_cache_sizeals volgt instellen:

SET effective_cache_size = '14 GB';   # e.g. on a dedicated server with 16 GB RAM

Stap 4: Los het probleem permanent op

U wilt waarschijnlijk ALTER SYSTEM SET ...of ALTER DATABASE db_name SET ...gebruiken om de nieuwe configuratieparameterwaarden permanent in te stellen (algemeen of per- databank). Zie de documentatievoor details over het instellen van parameters.

Stap 5: Aanvullende bronnen

Als het nog steeds niet werkt, wil je misschien ook een kijkje nemen op deze PostgreSQL Wiki pagina over server-tuning.


Antwoord 4, autoriteit 20%

Soms slaagt PostgreSQL er niet in om de beste keuze van indexen voor een bepaalde aandoening te maken. Stel dat er een transactietabel is met enkele miljoenen rijen, waarvan er enkele honderden zijn voor een bepaalde dag, en dat de tabel vier indexen heeft: transactie_id, cliënt_id, datum en beschrijving. U wilt de volgende query uitvoeren:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description = 'Refund'
GROUP BY client_id

PostgreSQL kan ervoor kiezen om de index transacties_description_idx te gebruiken in plaats van transacties_datum_idx, wat ertoe kan leiden dat de zoekopdracht enkele minuten duurt in plaats van minder dan een seconde. Als dit het geval is, kunt u het gebruik van de index op datum forceren door de voorwaarde als volgt te omzeilen:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description||'' = 'Refund'
GROUP BY client_id

Antwoord 5, autoriteit 9%

De vraag op zich is erg ongeldig. Forceren (door bijvoorbeeld enable_seqscan=off te doen) is een heel slecht idee. Het kan handig zijn om te controleren of het sneller zal zijn, maar productiecode zou dergelijke trucs nooit moeten gebruiken.

Leg in plaats daarvan de analyse van uw zoekopdracht uit, lees deze en ontdek waarom PostgreSQL voor een slecht (naar uw mening) plan kiest.

Er zijn tools op het web die helpen bij het lezen van uitvoer uit te leggen analyseren – een daarvan is explain.depesz.com– door mij geschreven.

Een andere optie is om lid te worden van het #postgresql-kanaal op het freenodeirc-netwerk, en daar met jongens te praten om je te helpen – omdat het optimaliseren van de zoekopdracht geen kwestie is van “stel een vraag, krijg antwoord, wees blij”. het is meer een gesprek, met veel dingen om te controleren, veel dingen om te leren.


Antwoord 6, autoriteit 3%

Eén ding om op te merken met PostgreSQL; waar u verwacht dat een index wordt gebruikt en deze wordt niet gebruikt, is de tabel VACUM ANALYSEREN.

VACUUM ANALYZE schema.table;

Hiermee worden statistieken bijgewerkt die door de planner worden gebruikt om de meest efficiënte manier te bepalen om een ​​query uit te voeren. Wat ertoe kan leiden dat de index wordt gebruikt.


Antwoord 7, autoriteit 2%

Er is een truc om postgres te pushen om de voorkeur te geven aan een seqscan door een OFFSET 0toe te voegen aan de subquery

Dit is handig voor het optimaliseren van verzoeken die grote/enorme tabellen koppelen wanneer u alleen de n eerste/laatste elementen nodig heeft.

Stel dat u op zoek bent naar de eerste/laatste 20 elementen met meerdere tabellen met 100.000 (of meer) vermeldingen, het heeft geen zin om alle query’s over alle gegevens te bouwen/koppelen wanneer u in de eerste zoekt 100 of 1000 inzendingen. In dit scenario blijkt het bijvoorbeeld meer dan 10x sneller te zijn om een ​​sequentiële scan uit te voeren.

zie Hoe kan ik voorkomen dat Postgres een subquery inline invult?


Antwoord 8

Indexen kunnen alleen onder bepaalde omstandigheden worden gebruikt.

  1. Het type waarde past bijvoorbeeld bij het type kolom.
  2. U voert geen bewerking uit op de kolom voordat u deze vergelijkt met de waarde.

Gegeven een klantentabel met 3 kolommen met 3 indexen op alle kolommen.

create table customer(id numeric(10), age int, phone varchar(200))

Het kan voorkomen dat de database probeert om bijvoorbeeld de index idx_age te gebruiken in plaats van het telefoonnummer.

Je kunt het gebruik van de indexleeftijd saboteren door een bewerking van leeftijd uit te voeren:

select * from customer where phone = '1235' and age+1 = 24 

(hoewel je op zoek bent naar de leeftijd van 23)

Dit is natuurlijk een heel eenvoudig voorbeeld en de intelligentie van postgres is waarschijnlijk goed genoeg om de juiste keuze te maken. Maar soms is er geen andere manier dan het systeem te misleiden.

Een ander voorbeeld is om

select * from customer where phone = '1235' and age::varchar = '23'

Maar dit is waarschijnlijk duurder dan de bovenstaande optie.

Helaas kunt u de naam van de index NIET in de query instellen zoals u kunt doen in MSSQL of Sybase.

select * from customer (index idx_phone) where phone = '1235' and age = 23.

Dit zou enorm helpen om dit soort problemen te voorkomen.


Antwoord 9

Blijkbaar zijn er gevallen waarin Postgre kan worden gesuggereerd om een ​​index te gebruiken door een vergelijkbare voorwaarde twee keer te herhalen.

Het specifieke geval dat ik heb waargenomen, was het gebruik van de PostGIS gin-index en de ST_Withinpredikaat als volgt:

select *
from address
natural join city
natural join restaurant
where st_within(address.location, restaurant.delivery_area)
and restaurant.delivery_area ~ address.location

Merk op dat het eerste predikaat st_within(address.location, restaurant.delivery_area)automatisch door PostGIS wordt ontleed in (restaurant.delivery_area ~ address.location) AND _st_contains(restaurant.delivery_area, address.location)dus het toevoegen van het tweede predikaat restaurant.delivery_area ~ address.locationis volledig overbodig. Desalniettemin overtuigde het tweede predikaat de planner om ruimtelijke index te gebruiken op address.locationen in het specifieke geval dat ik nodig had, verbeterde de looptijd 8 keer.

Other episodes