Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id CF1196330C7 for ; Thu, 18 Mar 2010 21:09:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 45739-05 for ; Fri, 19 Mar 2010 00:08:44 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 848DF632B5E for ; Thu, 18 Mar 2010 21:08:51 -0300 (ADT) Received: by bwz8 with SMTP id 8so2647685bwz.3 for ; Thu, 18 Mar 2010 17:08:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=mpGT5j/XJJN2PakvQRpN6RcWhEo3cmchoMf/IimfffI=; b=HNBlPR+0Py7NyUaO/vRrEZ8iYrvlm4Sm2WeXfCHlA+nZrlJOlgBxCLleMrHN6vMGIR tRleCUEq6RWzvhI1Iv8VQNNryOVqg5KKLYkoSrurXJA35ODKUZwlfDGfRVqFZgzt5hwk OLEw7XufswfYX6kK6MobtwEseGoj8fV1j3RR0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=jnaETajOVPXr9l41VAdaRUyfBZdh8EMlxc6HY19bW650WzdzDR5HuXMbGXrD2JEoGq /W44LbqWoz2rTmfP0uZCEk+SpDqV4swsLbyXCDfaYY4+nwHPBv1GJjKt6S04R+dehG8o 3ymh6+ySUGkyyXRnU9Un2LYCe+XM7rcIBWKBs= MIME-Version: 1.0 Received: by 10.204.131.82 with SMTP id w18mr2166059bks.29.1268957329277; Thu, 18 Mar 2010 17:08:49 -0700 (PDT) In-Reply-To: <1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com> References: <4BA148CF.5080701@r-stream.com> <1CFD7891521AAB4E8201FB7A78C9D36F05453FD7@mail-001.corp.automotive.com> Date: Thu, 18 Mar 2010 19:08:49 -0500 Message-ID: Subject: Re: Forcing index scan on query produces 16x faster From: Dave Crooke To: "Eger, Patrick" Cc: pgsql-performance Content-Type: multipart/alternative; boundary=00151747bd3ad8c35104821c2627 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.403 tagged_above=-10 required=5 tests=AWL=0.195, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/232 X-Sequence-Number: 38057 --00151747bd3ad8c35104821c2627 Content-Type: text/plain; charset=ISO-8859-1 I've also observed the same behaviour on a very large table (200GB data, 170GB for 2 indexes) .... I have a table which has 6 small columns, let's call them (a, b, c, d, e, f) and about 1 billion rows. There is an index on (a, b, c, d) - not my idea, Hibernate requires primary keys for every table. If I do the following query: *select max(c) from tbl where a=[constant literal] and b=[other constant literal];* .... then with maxed out analysis histograms, and no changes to any of the page_cost type stuff, it still deparately wants toi do a full table scan, which is ... kinda slow. Of course, a billion row table is also rather suboptimal (our app collects a lot more data than it used to) and so I'm bypassing Hibernate, and sharding it all by time, so that the tables and indexes will be a manageable size, and will also be vacuum-free as my aging out process is now DROP TABLE :-) Cheers Dave On Wed, Mar 17, 2010 at 8:01 PM, Eger, Patrick wrote: > I'm running 8.4.2 and have noticed a similar heavy preference for > sequential scans and hash joins over index scans and nested loops. Our > database is can basically fit in cache 100% so this may not be > applicable to your situation, but the following params seemed to help > us: > > seq_page_cost = 1.0 > random_page_cost = 1.01 > cpu_tuple_cost = 0.0001 > cpu_index_tuple_cost = 0.00005 > cpu_operator_cost = 0.000025 > effective_cache_size = 1000MB > shared_buffers = 1000MB > > > Might I suggest the Postgres developers reconsider these defaults for > 9.0 release, or perhaps provide a few sets of tuning params for > different workloads in the default install/docs? The cpu_*_cost in > particular seem to be way off afaict. I may be dead wrong though, fwiw > =) > > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Christian > Brink > Sent: Wednesday, March 17, 2010 2:26 PM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Forcing index scan on query produces 16x faster > > I am running into a problem with a particular query. The execution plan > cost shows that the Seq Scan is a better bet (cost=54020.49..54020.55) > over the forced index 'enable_seqscan = false' > (cost=1589703.87..1589703.93). But when I run the query both ways I get > a vastly different result (below). It appears not to want to bracket the > > salesitems off of the 'id' foreign_key unless I force it. > > Is there a way to rewrite or hint the planner to get me the better plan > without resorting to 'enable_seqscan' manipulation (or am I missing > something)? > > postream=> select version(); > version > ------------------------------------------------------------------------ > ------------------------------------------------- > PostgreSQL 8.0.3 on i386-redhat-linux-gnu, compiled by GCC > i386-redhat-linux-gcc (GCC) 4.0.0 20050505 (Red Hat 4.0.0-4) > > > postream=> SET enable_seqscan = false; > SET > postream=> EXPLAIN ANALYZE > postream-> SELECT si.group1_id as name, sum(si.qty) as count, > sum(si.amt) as amt > postream-> FROM salesitems si, sales s, sysstrings > postream-> WHERE si.id = s.id > postream-> AND si.group1_id != '' > postream-> AND si.group1_id IS NOT NULL > postream-> AND NOT si.void > postream-> AND NOT s.void > postream-> AND NOT s.suspended > postream-> AND s.tranzdate >= (cast('2010-02-15' as date) + > cast(sysstrings.data as time)) > postream-> AND s.tranzdate < ((cast('2010-02-15' as date) + 1) + > cast(sysstrings.data as time)) > postream-> AND sysstrings.id='net/Console/Employee/Day End Time' > postream-> GROUP BY name; > > QUERY PLAN > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ---------------------------------------------------- > HashAggregate (cost=1589703.87..1589703.93 rows=13 width=35) (actual > time=33.414..33.442 rows=12 loops=1) > -> Nested Loop (cost=0.01..1588978.22 rows=96753 width=35) (actual > > time=0.284..22.115 rows=894 loops=1) > -> Nested Loop (cost=0.01..2394.31 rows=22530 width=4) > (actual time=0.207..4.671 rows=225 loops=1) > -> Index Scan using sysstrings_pkey on sysstrings > (cost=0.00..5.78 rows=1 width=175) (actual time=0.073..0.078 rows=1 > loops=1) > Index Cond: (id = 'net/Console/Employee/Day End > Time'::text) > -> Index Scan using sales_tranzdate_index on sales s > (cost=0.01..1825.27 rows=22530 width=12) (actual time=0.072..3.464 > rows=225 loops=1) > Index Cond: ((s.tranzdate >= ('2010-02-15'::date + > > ("outer".data)::time without time zone)) AND (s.tranzdate < > ('2010-02-16'::date + ("outer".data)::time without time zone))) > Filter: ((NOT void) AND (NOT suspended)) > -> Index Scan using salesitems_pkey on salesitems si > (cost=0.00..70.05 rows=30 width=39) (actual time=0.026..0.052 rows=4 > loops=225) > Index Cond: (si.id = "outer".id) > Filter: ((group1_id <> ''::text) AND (group1_id IS NOT > NULL) AND (NOT void)) > Total runtime: 33.734 ms > (12 rows) > > postream=> SET enable_seqscan = true; > SET > postream=> EXPLAIN ANALYZE > postream-> SELECT si.group1_id as name, sum(si.qty) as count, > sum(si.amt) as amt > postream-> FROM salesitems si, sales s, sysstrings > postream-> WHERE si.id = s.id > postream-> AND si.group1_id != '' > postream-> AND si.group1_id IS NOT NULL > postream-> AND NOT si.void > postream-> AND NOT s.void > postream-> AND NOT s.suspended > postream-> AND s.tranzdate >= (cast('2010-02-15' as date) + > cast(sysstrings.data as time)) > postream-> AND s.tranzdate < ((cast('2010-02-15' as date) + 1) + > cast(sysstrings.data as time)) > postream-> AND sysstrings.id='net/Console/Employee/Day End Time' > postream-> GROUP BY name; > > QUERY PLAN > ------------------------------------------------------------------------ > ------------------------------------------------------------------------ > ---------------------------------------------------------- > HashAggregate (cost=54020.49..54020.55 rows=13 width=35) (actual > time=5564.929..5564.957 rows=12 loops=1) > -> Hash Join (cost=2539.63..53294.84 rows=96753 width=35) (actual > time=5502.324..5556.262 rows=894 loops=1) > Hash Cond: ("outer".id = "inner".id) > -> Seq Scan on salesitems si (cost=0.00..30576.60 > rows=885215 width=39) (actual time=0.089..3099.453 rows=901249 loops=1) > Filter: ((group1_id <> ''::text) AND (group1_id IS NOT > NULL) AND (NOT void)) > -> Hash (cost=2394.31..2394.31 rows=22530 width=4) (actual > time=3.329..3.329 rows=0 loops=1) > -> Nested Loop (cost=0.01..2394.31 rows=22530 width=4) > > (actual time=0.217..2.749 rows=225 loops=1) > -> Index Scan using sysstrings_pkey on > sysstrings (cost=0.00..5.78 rows=1 width=175) (actual time=0.077..0.085 > > rows=1 loops=1) > Index Cond: (id = 'net/Console/Employee/Day > End Time'::text) > -> Index Scan using sales_tranzdate_index on > sales s (cost=0.01..1825.27 rows=22530 width=12) (actual > time=0.074..1.945 rows=225 loops=1) > Index Cond: ((s.tranzdate >= > ('2010-02-15'::date + ("outer".data)::time without time zone)) AND > (s.tranzdate < ('2010-02-16'::date + ("outer".data)::time without time > zone))) > Filter: ((NOT void) AND (NOT suspended)) > Total runtime: 5565.262 ms > (13 rows) > > > -- > Christian Brink > > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@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 > --00151747bd3ad8c35104821c2627 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I've also observed the same behaviour on a very large table (200GB data= , 170GB for 2 indexes) ....

I have a table which has 6 small column= s, let's call them (a, b, c, d, e, f) and about 1 billion rows. There i= s an index on (a, b, c, d) - not my idea, Hibernate requires primary keys f= or every table.

If I do the following query:

select max(c) from tbl where a=3D[constant literal] and= b=3D[other constant literal];

.... then with maxed out a= nalysis histograms, and no changes to any of the page_cost type stuff, it s= till deparately wants toi do a full table scan, which is ... kinda slow.
Of course, a billion row table is also rather suboptimal (our app colle= cts a lot more data than it used to) and so I'm bypassing Hibernate, an= d sharding it all by time, so that the tables and indexes will be a managea= ble size, and will also be vacuum-free as my aging out process is now DROP = TABLE :-)

Cheers
Dave

On Wed, Mar 17, 2010 a= t 8:01 PM, Eger, Patrick <peger@automotive.com> wrote:
I'm running 8.4.2 and have noticed a similar heavy preference for
sequential scans and hash joins over index scans and nested loops. =A0Our database is can basically fit in cache 100% so this may not be
applicable to your situation, but the following params seemed to help
us:

seq_page_cost =3D 1.0
random_page_cost =3D 1.01
cpu_tuple_cost =3D 0.0001
cpu_index_tuple_cost =3D 0.00005
cpu_operator_cost =3D 0.000025
effective_cache_size =3D 1000MB
shared_buffers =3D 1000MB


Might I suggest the Postgres developers reconsider these defaults for
9.0 release, or perhaps provide a few sets of tuning params for
different workloads in the default install/docs? The cpu_*_cost in
particular seem to be way off afaict. I may be dead wrong though, fwiw
=3D)

-----Original Message-----
From: pgsql-perfo= rmance-owner@postgresql.org
[mailto:pgsql-per= formance-owner@postgresql.org] On Behalf Of Christian
Brink
Sent: Wednesday, March 17, 2010 2:26 PM
To: pgsql-performance@p= ostgresql.org
Subject: [PERFORM] Forcing index scan on query produces 16x faster

I am running into a problem with a particular query. The execution plan
cost shows that the Seq Scan is a better bet (cost=3D54020.49..54020.55) over the forced index 'enable_seqscan =3D =A0false'
(cost=3D1589703.87..1589703.93). But when I run the query both ways I get a vastly different result (below). It appears not to want to bracket the
salesitems off of the 'id' foreign_key unless I force it.

Is there a way to rewrite or hint the planner to get me the better plan
without resorting to 'enable_seqscan' manipulation (or am I missing=
something)?

postream=3D> select version();
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0version
------------------------------------------------------------------------ -------------------------------------------------
=A0PostgreSQL 8.0.3 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 4.0.0 20050505 (Red Hat 4.0.0-4)


postream=3D> SET enable_seqscan =3D false;
SET
postream=3D> EXPLAIN ANALYZE
postream-> SELECT si.group1_id as name, sum(si.qty) as count,
sum(si.amt) as amt
postream-> =A0 FROM salesitems si, sales s, sysstrings
postream-> =A0WHERE si.id= =3D s.id
postream-> =A0 =A0AND si.group1_id !=3D ''
postream-> =A0 =A0AND si.group1_id IS NOT NULL
postream-> =A0 =A0AND NOT si.void
postream-> =A0 =A0AND NOT s.void
postream-> =A0 =A0AND NOT s.suspended
postream-> =A0 =A0AND s.tranzdate >=3D (cast('2010-02-15' as = date) +
cast(sysstrings.data as time))
postream-> =A0 =A0AND s.tranzdate < ((cast('2010-02-15' as da= te) + 1) +
cast(sysstrings.data as time))
postream-> =A0 =A0AND sysstrings.id=3D'net/Console/Employee/Day End Time'
postream-> =A0GROUP BY name;

QUERY PLAN
------------------------------------------------------------------------ ------------------------------------------------------------------------ ----------------------------------------------------
=A0HashAggregate =A0(cost=3D1589703.87..1589703.93 rows=3D13 width=3D35) (= actual
time=3D33.414..33.442 rows=3D12 loops=3D1)
=A0 =A0-> =A0Nested Loop =A0(cost=3D0.01..1588978.22 rows=3D96753 width= =3D35) (actual

time=3D0.284..22.115 rows=3D894 loops=3D1)
=A0 =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D0.01..2394.31 rows=3D22= 530 width=3D4)
(actual time=3D0.207..4.671 rows=3D225 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using sysstrings_pkey o= n sysstrings
(cost=3D0.00..5.78 rows=3D1 width=3D175) (actual time=3D0.073..0.078 rows= =3D1
loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (id =3D 'net/Co= nsole/Employee/Day End
Time'::text)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using sales_tranzdate_i= ndex on sales s
(cost=3D0.01..1825.27 rows=3D22530 width=3D12) (actual time=3D0.072..3.464<= br> rows=3D225 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: ((s.tranzdate >= =3D ('2010-02-15'::date +

("outer".data)::time without time zone)) AND (s.tranzdate < ('2010-02-16'::date + ("outer".data)::time without time z= one)))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((NOT void) AND (NOT su= spended))
=A0 =A0 =A0 =A0 =A0-> =A0Index Scan using salesitems_pkey on salesitems= si
(cost=3D0.00..70.05 rows=3D30 width=3D39) (actual time=3D0.026..0.052 rows= =3D4
loops=3D225)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (si.id =3D "outer".id)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((group1_id <> ''::te= xt) AND (group1_id IS NOT
NULL) AND (NOT void))
=A0Total runtime: 33.734 ms
(12 rows)

postream=3D> SET enable_seqscan =3D true;
SET
postream=3D> EXPLAIN ANALYZE
postream-> SELECT si.group1_id as name, sum(si.qty) as count,
sum(si.amt) as amt
postream-> =A0 FROM salesitems si, sales s, sysstrings
postream-> =A0WHERE si.id= =3D s.id
postream-> =A0 =A0AND si.group1_id !=3D ''
postream-> =A0 =A0AND si.group1_id IS NOT NULL
postream-> =A0 =A0AND NOT si.void
postream-> =A0 =A0AND NOT s.void
postream-> =A0 =A0AND NOT s.suspended
postream-> =A0 =A0AND s.tranzdate >=3D (cast('2010-02-15' as = date) +
cast(sysstrings.data as time))
postream-> =A0 =A0AND s.tranzdate < ((cast('2010-02-15' as da= te) + 1) +
cast(sysstrings.data as time))
postream-> =A0 =A0AND sysstrings.id=3D'net/Console/Employee/Day End Time'
postream-> =A0GROUP BY name;

QUERY PLAN
------------------------------------------------------------------------ ------------------------------------------------------------------------ ----------------------------------------------------------
=A0HashAggregate =A0(cost=3D54020.49..54020.55 rows=3D13 width=3D35) (actu= al
time=3D5564.929..5564.957 rows=3D12 loops=3D1)
=A0 =A0-> =A0Hash Join =A0(cost=3D2539.63..53294.84 rows=3D96753 width= =3D35) (actual
time=3D5502.324..5556.262 rows=3D894 loops=3D1)
=A0 =A0 =A0 =A0 =A0Hash Cond: ("outer".id =3D "inner".= id)
=A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on salesitems si =A0(cost=3D0.00..305= 76.60
rows=3D885215 width=3D39) (actual time=3D0.089..3099.453 rows=3D901249 loop= s=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((group1_id <> ''::te= xt) AND (group1_id IS NOT
NULL) AND (NOT void))
=A0 =A0 =A0 =A0 =A0-> =A0Hash =A0(cost=3D2394.31..2394.31 rows=3D22530 = width=3D4) (actual
time=3D3.329..3.329 rows=3D0 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Nested Loop =A0(cost=3D0.01..2394.= 31 rows=3D22530 width=3D4)

(actual time=3D0.217..2.749 rows=3D225 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using sysst= rings_pkey on
sysstrings =A0(cost=3D0.00..5.78 rows=3D1 width=3D175) (actual time=3D0.077= ..0.085

rows=3D1 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (id =3D= 'net/Console/Employee/Day
End Time'::text)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using sales= _tranzdate_index on
sales s =A0(cost=3D0.01..1825.27 rows=3D22530 width=3D12) (actual
time=3D0.074..1.945 rows=3D225 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: ((s.tra= nzdate >=3D
('2010-02-15'::date + ("outer".data)::time without time z= one)) AND
(s.tranzdate < ('2010-02-16'::date + ("outer".data)::t= ime without time
zone)))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((NOT void)= AND (NOT suspended))
=A0Total runtime: 5565.262 ms
(13 rows)


--
Christian Brink



--
Sent via pgsql-performance mailing list
(pgsql-performance@post= gresql.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

--00151747bd3ad8c35104821c2627--