Re: query using incorrect index - Mailing list pgsql-performance

From Russell Keane
Subject Re: query using incorrect index
Date
Msg-id 8D0E5D045E36124A8F1DDDB463D548557CEF4D2FAD@mxsvr1.is.inps.co.uk
Whole thread Raw
In response to Re: query using incorrect index  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Responses Re: query using incorrect index  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
List 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 by date:

Previous
From: Russell Keane
Date:
Subject: Re: query using incorrect index
Next
From: "Kevin Grittner"
Date:
Subject: Re: query using incorrect index