Skip site navigation (1) Skip section navigation (2)

Re: query using incorrect index

From: Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)uk>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: query using incorrect index
Date: 2012-08-03 10:00:29
Message-ID: 8D0E5D045E36124A8F1DDDB463D548557CEF4D2FAD@mxsvr1.is.inps.co.uk (view raw or flat)
Thread:
Lists: 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 one on 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.040 rows=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(dot)Grittner(at)wicourts(dot)gov] 
Sent: 02 August 2012 21:13
To: Russell Keane; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] query using incorrect index

Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)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 communicated by 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

In response to

Responses

pgsql-performance by date

Next:From: Laszlo NagyDate: 2012-08-03 10:40:04
Subject: Re: Messed up time zones
Previous:From: Russell KeaneDate: 2012-08-03 09:50:02
Subject: Re: query using incorrect index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group