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.Grittner@xxxxxxxxxxxx] Sent: 02 August 2012 21:13 To: Russell Keane; pgsql-performance@xxxxxxxxxxxxxx Subject: Re: query using incorrect index Russell Keane <Russell.Keane@xxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
