Re: query using incorrect index

Поиск
Список
Период
Сортировка
От Russell Keane
Тема Re: query using incorrect index
Дата
Msg-id 8D0E5D045E36124A8F1DDDB463D548557CEF4D2FAD@mxsvr1.is.inps.co.uk
обсуждение исходный текст
Ответ на Re: query using incorrect index  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Ответы Re: query using incorrect index
Список pgsql-performance
Settings query:
"version";"PostgreSQL 9.0.4, compiled by Visual C++ build 1500, 32-bit"
"bytea_output";"escape"
"client_encoding";"UNICODE"
"lc_collate";"English_United Kingdom.1252"
"lc_ctype";"English_United Kingdom.1252"
"listen_addresses";"*"
"log_destination";"stderr"
"log_duration";"off"
"log_line_prefix";"%t "
"log_min_duration_statement";"1ms"
"log_statement";"none"
"logging_collector";"on"
"max_connections";"100"
"max_stack_depth";"2MB"
"port";"5433"
"search_path";"prac_live_10112, prac_shared_10112, global"
"server_encoding";"UTF8"
"shared_buffers";"32MB"
"TimeZone";"Europe/London"
"work_mem";"1MB"

Hardware:
It's important to note that this is a (purposely) low spec development machine but the performance story is a similar
oneon our test setup which is a lot closer to our live environment. (I'm in the process of getting figures on this). 
E8400 Core 2 Duo (2.99GHz)
4GB ram
xp (latest sp and all updates)
1 300GB SATA2 drive with 170 GB free space

Explain analyse with both indexes present but without the limit (uses the correct index):

"Sort  (cost=12534.90..12534.97 rows=25 width=4) (actual time=0.055..0.055 rows=0 loops=1)"
"  Output: messageq_table.entity_id"
"  Sort Key: messageq_table.entity_id"
"  Sort Method:  quicksort  Memory: 17kB"
"  Buffers: shared read=3"
"  ->  Bitmap Heap Scan on prac_live_10112.messageq_table  (cost=174.09..12534.32 rows=25 width=4) (actual
time=0.040..0.040rows=0 loops=1)" 
"        Output: messageq_table.entity_id"
"        Recheck Cond: ((messageq_table.staff_ty = 2) AND (messageq_table.staff_id = 2))"
"        Filter: (messageq_table.incoming AND (NOT messageq_table.inactive) AND
(aud_status_to_flag(messageq_table.aud_status)= 1))" 
"        Buffers: shared read=3"
"        ->  Bitmap Index Scan on messageq4  (cost=0.00..174.08 rows=4920 width=0) (actual time=0.037..0.037 rows=0
loops=1)"
"              Index Cond: ((messageq_table.inactive = false) AND (messageq_table.staff_ty = 2) AND
(messageq_table.staff_id= 2) AND (messageq_table.incoming = true))" 
"              Buffers: shared read=3"
"Total runtime: 0.092 ms"



-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: 02 August 2012 21:13
To: Russell Keane; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] query using incorrect index

Russell Keane <Russell.Keane@inps.co.uk> wrote:

> Clearly the statistics are off somehow but I really don't know where
> to start.
>
> Any help you can give me would be very much appreciated.

It would help to know your more about your hardware and PostgreSQL configuration.  The latter can probably best be
communicatedby copy/paste of the results of the query on this page: 

http://wiki.postgresql.org/wiki/Server_Configuration

Can you also post the EXPLAIN ANALYZE output for the slow query with both indexes present but without the LIMIT clause?

-Kevin

В списке pgsql-performance по дате отправления:

Предыдущее
От: Russell Keane
Дата:
Сообщение: Re: query using incorrect index
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: query using incorrect index