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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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