Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bthZF-0005ta-NI for pgsql-performance@arkaria.postgresql.org; Mon, 10 Oct 2016 20:53:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bthZF-0007Oe-5R for pgsql-performance@arkaria.postgresql.org; Mon, 10 Oct 2016 20:53:01 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bthXZ-0005WK-1o for pgsql-performance@postgresql.org; Mon, 10 Oct 2016 20:51:17 +0000 Received: from mail-lf0-x232.google.com ([2a00:1450:4010:c07::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bthXV-0006br-Ed for pgsql-performance@postgresql.org; Mon, 10 Oct 2016 20:51:15 +0000 Received: by mail-lf0-x232.google.com with SMTP id l131so1853008lfl.2 for ; Mon, 10 Oct 2016 13:51:13 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=subject:to:references:cc:from:message-id:date:user-agent :mime-version:in-reply-to:content-transfer-encoding; bh=A/cUfB+ly4kSk4hg+jNM4RaSGFSvykEMVK4Sdsb/0ig=; b=kgD7LpCzeXPZkqgVNU4R5oYeXrGdg09kBdJujicdArDACUx4X7gE8yH248/TwwvlnX 7LAcCIG5ZWv2kg05j7CfdCvllS5POaJu2sZaGAKuPHfuYU99SIUmX3hQM3l5w96PAw6d WrINYljcBtUa3+KRQFj043D4QQu3fU96+eU8p0YrLyNBEV6foZGRiipbMcUu/sOpNU2Y C/0Yhl9cym5cntgOkks7agizmlbI59YOqXYxY79CfG6HEKtH55LzHxXoXKOg8SZ1sMLk nrgFLx6gsP09V6fyJMcSyoarxccKJfy/56C2o+QJTikRcIHa2+PzQRiQxEQOYDzhbsTg L3rg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:subject:to:references:cc:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding; bh=A/cUfB+ly4kSk4hg+jNM4RaSGFSvykEMVK4Sdsb/0ig=; b=GLTOCBa9e6Q7p/kubeqVBeTfg55A0+FPA4DioFIpAZHPdj6sBJ5MItZD2p5dFBYMVI Azm8cZNqfgkyc0c7QkKeX/U7QIv/2WSpOr1DJuTtpZV/WZLjSu9NB20JNknX3pMi2zaB Ad7fFETZLmZ6J2RCnSjx3ajRa3D1g1NX8jUdxhQF6xJ1ogfnNn/DsjFCrTGriI406Foq wO+Jxh/g4dur0YyNYvEd2am1oAc1Ehas1Gayd/dbAPJioWgboQi1JB7nF7YaIl6sEKfC WmIfvNsNbyqJpbFrOah62FQe/Af5kgY6oAqT5X5hWhX+QWH3BWWHZP0Xco/xBPFNPQC3 OJuw== X-Gm-Message-State: AA6/9RknHd9MFl9oaK9XNGBt/ecy2NGmT4GQRCc4TbzekU5oiEYAqr+75m+c/6V51tpXMw== X-Received: by 10.25.92.152 with SMTP id u24mr88503lfi.114.1476132670493; Mon, 10 Oct 2016 13:51:10 -0700 (PDT) Received: from [192.168.0.106] (89-74-203-139.dynamic.chello.pl. [89.74.203.139]) by smtp.googlemail.com with ESMTPSA id b71sm6974696lfb.42.2016.10.10.13.51.08 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 10 Oct 2016 13:51:09 -0700 (PDT) Subject: Re: Why query plan is different? To: Pavel Stehule , Andrzej Zawadzki References: Cc: postgres performance list From: Andrzej Zawadzki Message-ID: Date: Mon, 10 Oct 2016 22:51:07 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit X-Pg-Spam-Score: -0.3 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org
On 10.10.2016 19:09, Pavel Stehule wrote:


2016-10-10 17:31 GMT+02:00 Andrzej Zawadzki <zawadaa@wp.pl>:
Hi,
Today, I noticed strange situation:

The same query run on different servers has very different plan:

Q: SELECT b.* FROM kredytob b  WHERE pesel = '22222222222'  ORDER BY b.id DESC LIMIT 1

Slow plan:

"Limit  (cost=0.43..28712.33 rows=1 width=4) (actual time=2574.041..2574.044 rows=1 loops=1)"
"  Output: id"
"  Buffers: shared hit=316132 read=110001"
"  ->  Index Scan Backward using kredytob_pkey on public.kredytob b  (cost=0.43..3244444.80 rows=113 width=4) (actual time=2574.034..2574.034 rows=1 loops=1)"
"        Output: id"
"        Filter: (b.pesel = '22222222222'::bpchar)"
"        Rows Removed by Filter: 433609"

here is backward index scan with - lot of rows is thrown

Rows Removed by Filter: 433609"

probably index definition on these servers are different

No! That's binary copy of whole database.
Index are the same!
But, when I ask database without "ORDER..."
(SELECT b.id FROM kredytob b  WHERE pesel = '22222222222';)
 then:

"SLOW"

"Index Scan using kredytob_pesel_typkred_opclass_idx on public.kredytob b  (cost=0.43..115349.30 rows=113 width=4) (actual time=233.767..392.710 rows=5 loops=1)"
"  Output: id"
"  Index Cond: (b.pesel = '22222222222'::bpchar)"
"  Buffers: shared hit=19259"
"Planning time: 0.254 ms"
"Execution time: 392.761 ms"

"FAST"

"Index Scan using kredytob_pesel_typkred_opclass_idx on public.kredytob b  (cost=0.43..115240.10 rows=112 width=4) (actual time=378.737..836.208 rows=5 loops=1)"
"  Output: id"
"  Index Cond: (b.pesel = '22222222222'::bpchar)"
"  Buffers: shared read=19237"
"Planning time: 0.568 ms"
"Execution time: 836.261 ms"

So, index is used in both queries but when is "ORDER" then everything change...
Why?



regards

Pavel

 
"        Buffers: shared hit=316132 read=110001"
"Planning time: 0.414 ms"
"Execution time: 2574.139 ms"


Fast plan:
"Limit  (cost=115240.66..115240.66 rows=1 width=4) (actual time=463.275..463.276 rows=1 loops=1)"
"  Output: id"
"  Buffers: shared hit=14661 read=4576"
"  ->  Sort  (cost=115240.66..115240.94 rows=112 width=4) (actual time=463.271..463.271 rows=1 loops=1)"
"        Output: id"
"        Sort Key: b.id DESC"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        Buffers: shared hit=14661 read=4576"
"        ->  Index Scan using kredytob_pesel_typkred_opclass_idx on public.kredytob b  (cost=0.43..115240.10 rows=112 width=4) (actual time=311.347..463.183 rows=5 loops=1)"
"              Output: id"
"              Index Cond: (b.pesel = '22222222222'::bpchar)"
"              Buffers: shared hit=14661 read=4576"
"Planning time: 0.383 ms"
"Execution time: 463.324 ms"

Data is almost equal - "slow" has a few more rows in table. ("Fast" is a copy from 1 am today).
Why runtime is slower?

--
Andrzej Zawadzki