From: Helio Campos Mello de Andrade <helio.campos@gmail.com>
To: pgsql-performance@postgresql.org
Subject: significant slow down with various LIMIT
Date: Mon, 12 Apr 2010 07:23:20 -0300
Message-ID: <4bc2f49e.9615f10a.46f6.ffffd71b@mx.google.com> (raw)
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
##############################################################################################################
Regards....
-------- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: helio.campos@gmail.com
Subject: Re: significant slow down with various LIMIT
In-Reply-To: <4bc2f49e.9615f10a.46f6.ffffd71b@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox