significant slow down with various LIMIT - Mailing list pgsql-performance
From | Helio Campos Mello de Andrade |
---|---|
Subject | significant slow down with various LIMIT |
Date | |
Msg-id | 4bc2f49e.9615f10a.46f6.ffffd71b@mx.google.com Whole thread Raw |
List | pgsql-performance |
Andrey, - Another idea for your problem is the one Kevin gave in the message following: ##########################################################################################################################
> SELECT * FROM t_route > WHERE t_route.route_type_fk = 1 > limit 4;
This one scanned the t_route table until it found four rows that matched. It apparently didn't need to look at very many rows to find the four matches, so it was fast.
> SELECT * FROM t_route > WHERE t_route.route_type_fk = > (SELECT id FROM t_route_type WHERE type = 2) > limit 4;
This one came up with an id for a route type that didn't have any matches in the t_route table, so it had to scan the entire t_route table. (Based on your next query, the subquery probably returned NULL, so there might be room for some optimization here.) If you had chosen a route type with at least four matches near the start of the route table, this query would have completed quickly.
> SELECT * FROM t_route, t_route_type > WHERE t_route.route_type_fk = t_route_type.id > AND type = 2 > limit 4;
Since it didn't find any t_route_type row which matched, it knew there couldn't be any output from the JOIN, so it skipped the scan of the t_route table entirely. -Kevin##############################################################################################################
-------- Original Message --------
From: | - Fri Apr 9 17:36:41 2010 |
---|---|
X-Account-Key: | account3 |
X-UIDL: | GmailId127e449663a13d39 |
X-Mozilla-Status: | 0011 |
X-Mozilla-Status2: | 00000000 |
X-Mozilla-Keys: | |
Delivered-To: | helio.campos@gmail.com |
Received: | by 10.231.79.67 with SMTP id o3cs40933ibk; Fri, 9 Apr 2010 13:36:16 -0700 (PDT) |
Received: | by 10.114.248.22 with SMTP id v22mr967398wah.8.1270845368202; Fri, 09 Apr 2010 13:36:08 -0700 (PDT) |
Return-Path: | <pgsql-performance-owner+M38376@postgresql.org> |
Received: | from maia-1.hub.org (maia-1.hub.org [200.46.208.211]) by mx.google.com with ESMTP id 8si1947813ywh.11.2010.04.09.13.36.07; Fri, 09 Apr 2010 13:36:08 -0700 (PDT) |
Received-SPF: | neutral (google.com: 200.46.208.211 is neither permitted nor denied by best guess record for domain of pgsql-performance-owner+M38376@postgresql.org) client-ip=200.46.208.211; |
Authentication-Results: | mx.google.com; spf=neutral (google.com: 200.46.208.211 is neither permitted nor denied by best guess record for domain of pgsql-performance-owner+M38376@postgresql.org) smtp.mail=pgsql-performance-owner+M38376@postgresql.org |
Received: | from postgresql.org (mail.postgresql.org [200.46.204.86]) by maia-1.hub.org (Postfix) with ESMTP id 54BAEAFD1B6; Fri, 9 Apr 2010 20:36:00 +0000 (UTC) |
Received: | from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2E74B633047 for <pgsql-performance-postgresql.org@mail.postgresql.org>; Thu, 8 Apr 2010 22:36:17 -0300 (ADT) |
Received: | from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90832-06 for <pgsql-performance-postgresql.org@mail.postgresql.org>; Fri, 9 Apr 2010 01:36:06 +0000 (UTC) |
Received: | from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id BBD50632DC3 for <pgsql-performance@postgresql.org>; Thu, 8 Apr 2010 22:36:06 -0300 (ADT) |
Received: | from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.3/8.14.3) with ESMTP id o391a091050073 for <pgsql-performance@postgresql.org>; Thu, 8 Apr 2010 22:36:00 -0300 (ADT) (envelope-from news@news.hub.org) |
Received: | (from news@localhost) by news.hub.org (8.14.3/8.14.3/Submit) id o391DTvp041710 for pgsql-performance@postgresql.org; Thu, 8 Apr 2010 22:13:29 -0300 (ADT) (envelope-from news) |
From: | norn <andrey.perliev@gmail.com> |
X-Newsgroups: | pgsql.performance |
Subject: | Re: [PERFORM] significant slow down with various LIMIT |
Date: | Thu, 8 Apr 2010 18:13:33 -0700 (PDT) |
Organization: | http://groups.google.com |
Lines: | 72 |
Message-ID: | <8ae12099-1cbb-40d5-b7fc-c15b8deba021@30g2000yqi.googlegroups.com> |
References: | <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com> <4BBDC19A02000025000305A4@gw.wicourts.gov> |
Mime-Version: | 1.0 |
Content-Type: | text/plain; charset=ISO-8859-1 |
Content-Transfer-Encoding: | quoted-printable |
X-Complaints-To: | groups-abuse@google.com |
Complaints-To: | groups-abuse@google.com |
Injection-Info: | 30g2000yqi.googlegroups.com; posting-host=94.78.201.171; posting-account=woDzKwoAAACEqYut1Qq-BHNhLOB-6ihP |
User-Agent: | G2/1.0 |
X-HTTP-UserAgent: | Mozilla/5.0 (X11; U; Linux x86_64; en-US) AppleWebKit/533.4 (KHTML, like Gecko) Chrome/5.0.368.0 Safari/533.4,gzip(gfe) |
To: | pgsql-performance@postgresql.org |
X-Virus-Scanned: | Maia Mailguard 1.0.1 |
X-Spam-Status: | No, hits=-0.74 tagged_above=-10 required=5 tests=BAYES_20=-0.74 |
X-Spam-Level: | |
X-Mailing-List: | pgsql-performance |
List-Archive: | <http://archives.postgresql.org/pgsql-performance> |
List-Help: | <mailto:majordomo@postgresql.org?body=help> |
List-ID: | <pgsql-performance.postgresql.org> |
List-Owner: | <mailto:pgsql-performance-owner@postgresql.org> |
List-Post: | <mailto:pgsql-performance@postgresql.org> |
List-Subscribe: | <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance> |
List-Unsubscribe: | <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance> |
Precedence: | bulk |
Sender: | pgsql-performance-owner@postgresql.org |
Kevin, thanks for your attention! I've read SlowQueryQuestions, but anyway can't find bottleneck... Here requested information: OS: Ubuntu 9.10 64bit, Postgresql 8.4.2 with Postgis Hardware: AMD Phenom(tm) II X4 945, 8GB RAM, 2 SATA 750GB (pg db installed in software RAID 0) Please also note that this hardware isn't dedicated DB server, but also serve as web server and file server. I have about 3 million rows in core_object, 1.5 million in plugin_plugin_addr and 1.5 million in plugins_guide_address. When there were 300 000+ objects queries works perfectly, but as db enlarge things go worse... # select version(); PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.1-4ubuntu8) 4.4.1, 64-bit ---postgresql.conf--- data_directory = '/mnt/fast/postgresql/8.4/main' hba_file = '/etc/postgresql/8.4/main/pg_hba.conf' ident_file = '/etc/postgresql/8.4/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/8.4-main.pid' listen_addresses = 'localhost' port = 5432 max_connections = 250 unix_socket_directory = '/var/run/postgresql' ssl = true shared_buffers = 1024MB temp_buffers = 16MB work_mem = 128MB maintenance_work_mem = 512MB fsync = off wal_buffers = 4MB checkpoint_segments = 16 effective_cache_size = 1536MB log_min_duration_statement = 8000 log_line_prefix = '%t ' datestyle = 'iso, mdy' lc_messages = 'en_US.UTF-8' lc_monetary = 'en_US.UTF-8' lc_numeric = 'en_US.UTF-8' lc_time = 'en_US.UTF-8' default_text_search_config = 'pg_catalog.english' standard_conforming_strings = on escape_string_warning = off constraint_exclusion = on checkpoint_completion_target = 0.9 ---end postgresql.conf--- I hope this help! Any ideas are appreciated! On Apr 9, 12:44 am, Kevin.Gritt...@wicourts.gov ("Kevin Grittner") wrote: > > Could you show us the output from "select version();", describe your > hardware and OS, and show us the contents of your postgresql.conf > file (with all comments removed)? We can then give more concrete > advice than is possible with the information provided so far. > > http://wiki.postgresql.org/wiki/SlowQueryQuestions > > -Kevin > > -- > Sent via pgsql-performance mailing list (pgsql-performa...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
pgsql-performance by date: