Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 5D5A6D32489 for ; Mon, 30 Apr 2012 18:18:05 -0300 (ADT) Received: from mail-ob0-f174.google.com ([209.85.214.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SOxyu-0007K7-5Q for pgsql-performance@postgresql.org; Mon, 30 Apr 2012 21:18:05 +0000 Received: by obbta14 with SMTP id ta14so4602525obb.19 for ; Mon, 30 Apr 2012 14:17:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=date:from:to:message-id:subject:x-mailer:mime-version:content-type; bh=KDqnm7MkYaSndtn9Zyr4olQxIafAbDvinOcoFoB9F8s=; b=S75reRYp1HptzIjDunmlkKbTLtjRJPKGVOuA9+ZStmdWYgvFXaOL8gRsM22BS+SgTJ Fk0p+sQeCaY53nDXYU9Z4+Kb5x065W45QFb2gwUHJ5p+Ls2/P6PmbHg+MGwlGism5R1q xNtyM0GgifdNcvK0qKDymglMwht6VOJupFoHskQZmG2vOScWaMlmfPlmw4QFBeNou6ow k5uHr3+EtPAFE+Yoh4hRaRXXfzVRwgZnsSArcrogXlF4B3h2s7j7SHYPBiIM3oBpvscs s1zZOjpZf8qNDcdVWL2lqFKwu/FEXS0PteewHsh6sVke/cHEMJkZIU8kAwar9i9BJbk0 beRQ== Received: by 10.182.245.17 with SMTP id xk17mr29845904obc.66.1335820671237; Mon, 30 Apr 2012 14:17:51 -0700 (PDT) Received: from Blah-MacBook-Pro.local (ip98-168-159-225.ok.ok.cox.net. [98.168.159.225]) by mx.google.com with ESMTPS id gf8sm17942492obc.3.2012.04.30.14.17.48 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 30 Apr 2012 14:17:49 -0700 (PDT) Date: Mon, 30 Apr 2012 16:17:47 -0500 From: Josh Turmel To: pgsql-performance@postgresql.org Message-ID: Subject: Query got slow from 9.0 to 9.1 upgrade X-Mailer: sparrow 1.4.2 (build 959.4) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="4f9f017b_799d0247_d2a" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201205/20 X-Sequence-Number: 46844 --4f9f017b_799d0247_d2a Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline We just upgraded from 9.0 to 9.1, we're using the same server configuration, that has been confirmed 3 or 4 times over. Any help would be appreciated. If I remove the "ORDER BY" it gets fast again because it goes back to using the user_id index, if I remove the LIMIT/OFFSET it gets fast again, obviously I need both of those, but that was just to test and see what would happen. Query: SELECT * FROM bookmark_groups WHERE user_id = 6708929 ORDER BY created DESC LIMIT 25 OFFSET 0; explain analyze from 9.0: Limit (cost=1436.78..1436.84 rows=25 width=99) (actual time=15.399..15.403 rows=25 loops=1) -> Sort (cost=1436.78..1438.67 rows=757 width=99) (actual time=15.397..15.397 rows=25 loops=1) Sort Key: created Sort Method: top-N heapsort Memory: 28kB -> Index Scan using bookmark_groups_user_id_idx on bookmark_groups (cost=0.00..1415.42 rows=757 width=99) (actual time=0.011..9.953 rows=33868 loops=1) Index Cond: (user_id = 6708929) Total runtime: 15.421 ms explain analyze from 9.1: Limit (cost=0.00..1801.30 rows=25 width=101) (actual time=1565.071..5002.068 rows=25 loops=1) -> Index Scan using bookmark_groups_created_idx on bookmark_groups (cost=0.00..2592431.76 rows=35980 width=101) (actual time=1565.069..5002.062 rows=25 loops=1) Filter: (user_id = 6708929) Total runtime: 5002.095 ms DDL: CREATE TABLE "public"."bookmark_groups" ( "id" int8 NOT NULL DEFAULT nextval('bookmark_groups_id_seq'::regclass), "user_id" int4 NOT NULL DEFAULT NULL, "version" varchar DEFAULT NULL, "created" timestamp(6) WITH TIME ZONE NOT NULL DEFAULT now(), "username" varchar NOT NULL DEFAULT NULL, "labels" varchar DEFAULT NULL, "reference" varchar NOT NULL DEFAULT NULL, "human" varchar NOT NULL DEFAULT NULL, "highlight_color" char(6) DEFAULT NULL, "title" varchar DEFAULT NULL, "version_id" int4 NOT NULL DEFAULT NULL, CONSTRAINT "bookmark_groups_pkey1" PRIMARY KEY ("id", "reference") ) WITH (OIDS=FALSE); ALTER TABLE "public"."bookmark_groups" OWNER TO "dev"; CREATE INDEX "bookmark_groups_created_idx" ON "public"."bookmark_groups" USING btree(created DESC NULLS FIRST); CREATE INDEX "bookmark_groups_user_id_idx" ON "public"."bookmark_groups" USING btree(user_id ASC NULLS LAST); --4f9f017b_799d0247_d2a Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
We just upgraded from 9.0 to 9.1, we're using the sa= me server configuration, that has been confirmed 3 or 4 times over. Any h= elp would be appreciated. If I remove the =22ORDER BY=22 it gets fast aga= in because it goes back to using the user=5Fid index, if I remove the LIM= IT/O=46=46SET it gets fast again, obviously I need both of those, but tha= t was just to test and see what would happen.

Qu= ery: SELECT * =46ROM bookmark=5Fgroups WHERE user=5Fid =3D 6708929 O= RDER BY created DESC LIMIT 25 O=46=46SET 0;

expl= ain analyze from 9.0:

Limit  (cost=3D1= 436.78..1436.84 rows=3D25 width=3D99) (actual time=3D15.399..15.403 rows=3D= 25 loops=3D1)
  ->  Sort  (cost=3D1436.78..14= 38.67 rows=3D757 width=3D99) (actual time=3D15.397..15.397 rows=3D25 loop= s=3D1)
        Sort Key: created
=         Sort Method:  top-N heapsort  Memor= y: 28kB
        ->  Index Scan usin= g bookmark=5Fgroups=5Fuser=5Fid=5Fidx on bookmark=5Fgroups  (cost=3D= 0.00..1415.42 rows=3D757 width=3D99) (actual time=3D0.011..9.953 rows=3D3= 3868 loops=3D1)
             = ; Index Cond: (user=5Fid =3D 6708929)
Total runtime: 15.421 ms<= /div>

explain analyze from 9.1:

Limit  (cost=3D0.00..1801.30 rows=3D25 width=3D101) (a= ctual time=3D1565.071..5002.068 rows=3D25 loops=3D1)
  -&g= t;  Index Scan using bookmark=5Fgroups=5Fcreated=5Fidx on bookmark=5F= groups  (cost=3D0.00..2592431.76 rows=3D35980 width=3D101) (actual t= ime=3D1565.069..5002.062 rows=3D25 loops=3D1)
    &nb= sp;   =46ilter: (user=5Fid =3D 6708929)
Total runtime: 500= 2.095 ms

DDL:

CREATE TABLE =22public=22.=22bookmark=5Fgroups=22 (
=22id=22 = int8 NOT NULL DE=46AULT nextval('bookmark=5Fgroups=5Fid=5Fseq'::regclass)= ,
=22user=5Fid=22 int4 NOT NULL DE=46AULT NULL,
=22ve= rsion=22 varchar DE=46AULT NULL,
=22created=22 timestamp(6) WIT= H TIME ZONE NOT NULL DE=46AULT now(),
=22username=22 varchar NO= T NULL DE=46AULT NULL,
=22labels=22 varchar DE=46AULT NULL,
= =22reference=22 varchar NOT NULL DE=46AULT NULL,
=22huma= n=22 varchar NOT NULL DE=46AULT NULL,
=22highlight=5Fcolor=22 c= har(6) DE=46AULT NULL,
=22title=22 varchar DE=46AULT NULL,
<= /span>=22version=5Fid=22 int4 NOT NULL DE=46AULT NULL,
CONSTRAI= NT =22bookmark=5Fgroups=5Fpkey1=22 PRIMARY KEY (=22id=22, =22reference=22= )
)
WITH (OIDS=3D=46ALSE);
ALTER TABLE =22p= ublic=22.=22bookmark=5Fgroups=22 OWNER TO =22dev=22;
CREATE IND= EX =22bookmark=5Fgroups=5Fcreated=5Fidx=22 ON =22public=22.=22bookmark=5F= groups=22 USING btree(created DESC NULLS =46IRST);
CREATE INDEX= =22bookmark=5Fgroups=5Fuser=5Fid=5Fidx=22 ON =22public=22.=22bookmark=5F= groups=22 USING btree(user=5Fid ASC NULLS LAST);
--4f9f017b_799d0247_d2a--