Re: Query uses incorrect index - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: Query uses incorrect index
Date
Msg-id 4D1076EF020000250003888E@gw.wicourts.gov
Whole thread Raw
In response to Query uses incorrect index  (pasman pasmański <pasman.p@gmail.com>)
List pgsql-performance
pasman pasma*ski<pasman.p@gmail.com> wrote:

>                     ->  Index Scan using NTA_5" on "NumeryA" a
> (cost=0.01..10016.75 rows=24175 width=42) (actual
> time=0.132..308.018 rows=33597 loops=1)"

> seq_page_cost = 0.3
> random_page_cost = 0.5

Your data is heavily cached (to be able to read 33597 rows randomly
through an index in 308 ms), yet you're telling the optimizer that a
random access is significantly more expensive than a sequential one.
Try this in your session before running the query (with all indexes
present):

set seq_page_cost = 0.1;
set random_page_cost = 0.1;

I don't know if the data for all your queries is so heavily cached
-- if so, you might want to change these settings in your
postgresql.conf file.

-Kevin

pgsql-performance by date:

Previous
From: pasman pasmański
Date:
Subject: Query uses incorrect index
Next
From: Pavel Stehule
Date:
Subject: Re: MySQL HandlerSocket - Is this possible in PG?