From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Shards + hash = forever running queries
Date: Mon, 23 Jul 2012 11:07:45 -0400
Message-ID: <29911.1343056065@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+mi_8ZWi8MUvpuiqse+wYMreBF_j0vLDK35=H-ALwfVm3fPmA@mail.gmail.com>
References: <CA+mi_8ZWi8MUvpuiqse+wYMreBF_j0vLDK35=H-ALwfVm3fPmA@mail.gmail.com>
Daniele Varrazzo <daniele.varrazzo@gmail.com> writes:
> Since "big" was sharded, the query plan results in something like:
> Hash Join (cost=10000000001.23..30038997974.72 rows=10 width=753)
> Hash Cond: (b.id = i.big_id)
> -> Append (cost=0.00..20038552251.23 rows=118859245 width=11)
> -> Index Scan using big_201207_pkey on big_201207 b
> (cost=0.00..2224100.46 rows=1609634 width=12)
> -> Index Scan using big_201101_pkey on big_201101 b
> (cost=0.00..404899.71 rows=5437497 width=12)
> -> Index Scan using big_201104_pkey on big_201104 b
> (cost=0.00..349657.58 rows=4625181 width=12)
> -> [...all the shards]
> -> Hash (cost=10000000001.10..10000000001.10 rows=10 width=742)
> -> Seq Scan on small i (cost=10000000000.00..10000000001.10
> rows=10 width=742)
[ squint... ] 9.1 certainly ought to be able to find a smarter plan for
such a case. For instance, if I try this on 9.1 branch tip:
regression=# create table p (id int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "p_pkey" for table "p"
CREATE TABLE
regression=# create table c1 (primary key (id)) inherits(p);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "c1_pkey" for table "c1"
CREATE TABLE
regression=# create table c2 (primary key (id)) inherits(p);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "c2_pkey" for table "c2"
CREATE TABLE
regression=# explain select * from p,int4_tbl where id=f1;
QUERY PLAN
--------------------------------------------------------------------------------
Nested Loop (cost=0.00..53.25 rows=120 width=8)
Join Filter: (public.p.id = int4_tbl.f1)
-> Seq Scan on int4_tbl (cost=0.00..1.05 rows=5 width=4)
-> Append (cost=0.00..10.40 rows=3 width=4)
-> Index Scan using p_pkey on p (cost=0.00..1.87 rows=1 width=4)
Index Cond: (id = int4_tbl.f1)
-> Index Scan using c1_pkey on c1 p (cost=0.00..4.27 rows=1 width=4)
Index Cond: (id = int4_tbl.f1)
-> Index Scan using c2_pkey on c2 p (cost=0.00..4.27 rows=1 width=4)
Index Cond: (id = int4_tbl.f1)
(10 rows)
You have evidently got enable_seqscan turned off, so I wonder whether
the cost penalties applied by that are swamping the estimates. Do you
get any better results if you re-enable that?
regards, tom lane
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: tgl@sss.pgh.pa.us, daniele.varrazzo@gmail.com
Subject: Re: Shards + hash = forever running queries
In-Reply-To: <29911.1343056065@sss.pgh.pa.us>
* 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