Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id D2C9F63339A for ; Tue, 3 Mar 2009 13:12:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 22920-01 for ; Tue, 3 Mar 2009 13:12:51 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.25]) by mail.postgresql.org (Postfix) with ESMTP id 26EFC632FFB for ; Tue, 3 Mar 2009 13:12:51 -0400 (AST) Received: by qw-out-2122.google.com with SMTP id 5so2175836qwd.61 for ; Tue, 03 Mar 2009 09:12:50 -0800 (PST) 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 :content-transfer-encoding; bh=dsvQW5sdOvf9ngmJc7YZ74f+LQqhtP2OUp9HHOOf4aA=; b=g1yiCgM9V8yoXCQI5b0IhqgCEmkgmYx+EgFauVH965lsGGC9BrL7oAdzyQmKSZUZy6 a8ooc/KomtlJ5/CXVxLFqraXnEaX1zOkf1aWoQtqHTJKMp8e3VwQJ3CVwSOuPdqJ+QVf 1nhyGjQiG2Qb0Czw6RiVQ5zW2HnUNWrMSfdsw= 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:content-transfer-encoding; b=HAI4EUf2IHpvDSc2mGlN0+3lRVn8l77qZ+LyQVgAptEXvihbzMwxWUtnbwSOuMFhn5 a0a65Rq+MvNBB7wyS2Tpl1h3jbBV3BWIuxWmDpncrrem6lSd64Ue+JYgQ307ZYZ0ujKt qH+7HH8cfeaeXY9Fdsql7XKUXUWyoO3Hrkcc4= MIME-Version: 1.0 Received: by 10.229.73.141 with SMTP id q13mr3971425qcj.15.1236100369303; Tue, 03 Mar 2009 09:12:49 -0800 (PST) In-Reply-To: References: Date: Tue, 3 Mar 2009 12:12:48 -0500 Message-ID: <603c8f070903030912t36c541ebr50b4576c7a9a5b65@mail.gmail.com> Subject: Re: Problems with ordering (can't force query planner to use an index) From: Robert Haas To: Sebastjan Trepca Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/25 X-Sequence-Number: 32947 On Tue, Mar 3, 2009 at 12:05 PM, Sebastjan Trepca wrote: > Hey, > > I have a table that links content together and it currently holds > about 17 mio records. Typical query is a join with a content table and > link table: > > noovo-new=3D# explain analyze SELECT "core_accessor"."id", > "core_accessor"."content_type_id", > "core_accessor"."object_id", "core_accessor"."ordering", > "core_accessor"."label", "core_accessor"."date_posted", > "core_accessor"."publish_state", "core_accessor"."nooximity_old", > "core_accessor"."rising", "core_accessor"."nooximity", > "core_accessor"."nooximity_old_date_posted", > "core_accessor"."nooximity_date_posted", "core_accessor"."user_id", > "core_accessor"."slot_id", "core_accessor"."slot_type_id", > "core_accessor"."role", "core_base"."object_id", > "core_base"."content_type_id", "core_base"."abstract", > "core_base"."abstract_title", "core_base"."image", > =A0"core_base"."date_posted", "core_base"."date_modified", > "core_base"."date_expires", "core_base"."publish_state", > =A0"core_base"."location", "core_base"."location_x", > "core_base"."location_y", "core_base"."raw", "core_base"."author_id", > =A0"core_base"."excerpt", "core_base"."state_id", > "core_base"."country_id", "core_base"."language", > "core_base"."_identifier", > =A0"core_base"."slot_url", "core_base"."source_id", > "core_base"."source_content_type_id", "core_base"."source_type", > =A0"core_base"."source_value", "core_base"."source_title", > "core_base"."direct_to_source", "core_base"."comment_count", > =A0"core_base"."public" FROM "core_accessor" INNER JOIN core_base AS > core_base ON core_base.content_type_id =3D > =A0core_accessor.content_type_id AND core_base.object_id =3D > core_accessor.object_id WHERE (("core_accessor"."slot_type_id" =3D 119 > =A0AND "core_accessor"."slot_id" =3D 472 AND "core_accessor"."label" =3D = E'' > AND "core_accessor"."publish_state" >=3D 60 AND > =A0"core_accessor"."role" IN (0) AND "core_accessor"."user_id" =3D 0)) > order by core_accessor.date_posted, core_accessor.nooximity LIMIT 5 > ; > > =A0 =A0 =A0QUERY PLAN > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- > =A0Limit =A0(cost=3D31930.65..31930.66 rows=3D5 width=3D860) (actual > time=3D711.924..711.927 rows=3D5 loops=3D1) > =A0 -> =A0Sort =A0(cost=3D31930.65..31937.80 rows=3D2861 width=3D860) (ac= tual > time=3D711.923..711.923 rows=3D5 loops=3D1) > =A0 =A0 =A0 =A0 Sort Key: core_accessor.date_posted, core_accessor.nooxim= ity > =A0 =A0 =A0 =A0 Sort Method: =A0top-N heapsort =A0Memory: 31kB > =A0 =A0 =A0 =A0 -> =A0Nested Loop =A0(cost=3D0.00..31883.13 rows=3D2861 w= idth=3D860) > (actual time=3D0.089..543.497 rows=3D68505 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using core_accessor_fresh_id= x on > core_accessor =A0(cost=3D0.00..5460.07 rows=3D2970 width=3D92) (actual > time=3D0.068..54.921 rows=3D69312 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((slot_id =3D 472) AN= D (slot_type_id =3D > 119) AND (label =3D ''::text) AND (user_id =3D 0) AND (role =3D 0) AND > (publish_state >=3D 60)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using core_base_pkey on core= _base > (cost=3D0.00..8.88 rows=3D1 width=3D768) (actual time=3D0.004..0.005 rows= =3D1 > loops=3D69312) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((core_base.object_id= =3D > core_accessor.object_id) AND (core_base.content_type_id =3D > core_accessor.content_type_id)) > =A0Total runtime: 712.031 ms > (10 rows) > > noovo-new=3D# select * from pg_stat_user_tables where relname=3D'core_acc= essor'; > =A0relid | schemaname | =A0 =A0relname =A0 =A0| seq_scan | seq_tup_read | > idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | > n_tup_hot_upd | n_live_tup | n_dead_tup | =A0 =A0 =A0 =A0 =A0last_vacuum > =A0| last_autovacuum | =A0 =A0 =A0 =A0 last_analyze =A0 =A0 =A0 =A0 =A0| = last_autoanalyze > -------+------------+---------------+----------+--------------+----------= +---------------+-----------+-----------+-----------+---------------+------= ------+------------+-------------------------------+-----------------+-----= --------------------------+------------------ > =A051159 | public =A0 =A0 | core_accessor | =A0 =A0 =A0 58 | =A0 =A074977= 3516 | > 13785608 | =A0 =A0 149165183 | =A0 =A0 =A09566 | =A0 =A0 =A0 548 | =A0 = =A0 =A0 347 | > =A0206 | =A0 17144303 | =A0 =A0 =A0 =A0251 | 2009-03-03 07:02:19.733778-0= 6 | > =A0 =A0 =A0 =A0 =A0 | 2009-03-03 06:17:47.784268-06 | > (1 row) > > noovo-new=3D# \d+ core_accessor; > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0Table "public.core_accessor" > =A0 =A0 =A0 =A0 =A0Column =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 Type = =A0 =A0 =A0 =A0 =A0 | > =A0 =A0 =A0 =A0 Modifiers =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0| Description > ---------------------------+--------------------------+------------------= ------------------------------------------+------------- > =A0id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| bigint =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 | not null > default nextval('core_accessor_id_seq'::regclass) | > =A0flavor =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| character varying(32) = =A0 =A0| > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0content_type_id =A0 =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0| not null > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0object_id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0| not null > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0publish_state =A0 =A0 =A0 =A0 =A0 =A0 | smallint =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 | not null > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0date_posted =A0 =A0 =A0 =A0 =A0 =A0 =A0 | timestamp with time zone | n= ot null > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0user_id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =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 =A0 =A0 =A0| > =A0slot_id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =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 =A0 =A0 =A0| > =A0slot_type_id =A0 =A0 =A0 =A0 =A0 =A0 =A0| integer =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 =A0 =A0 =A0| > =A0role =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| smallint =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 =A0 =A0| > =A0ordering =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| integer =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 =A0 =A0 =A0| > =A0author_id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =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 =A0 =A0 =A0| > =A0nooximity_old =A0 =A0 =A0 =A0 =A0 =A0 | double precision =A0 =A0 =A0 = =A0 | default 0.0 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0rising =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| double precision =A0 = =A0 =A0 =A0 | default 0.0 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0label =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | text =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 = =A0 =A0 =A0 =A0| > =A0nooximity =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | double precision =A0 =A0 = =A0 =A0 | not null > default 1.0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 | > =A0nooximity_old_date_posted | timestamp with time zone | > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > =A0nooximity_date_posted =A0 =A0 | timestamp with time zone | > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| > Indexes: > =A0 =A0"portal_metainfo_pkey" PRIMARY KEY, btree (id) > =A0 =A0"portal_metainfo_unique_constr" UNIQUE, btree (content_type_id, > object_id, user_id, slot_id, slot_type_id, role, label) CLUSTER > =A0 =A0"core_accessor_date_idx" btree (date_posted, nooximity) > =A0 =A0"core_accessor_dated_idx" btree (slot_id, slot_type_id, label, > user_id, role, publish_state, date_posted, nooximity) > =A0 =A0"core_accessor_fresh_idx" btree (slot_id, slot_type_id, label, > user_id, role, publish_state) > =A0 =A0"core_accessor_popularity_idx" btree (nooximity, date_posted) > Check constraints: > =A0 =A0"portal_metainfo_object_id_check" CHECK (object_id >=3D 0) > =A0 =A0"portal_metainfo_owner_id_check" CHECK (slot_id >=3D 0) > Foreign-key constraints: > =A0 =A0"portal_metainfo_accessor_id_fkey" FOREIGN KEY (user_id) > REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED > =A0 =A0"portal_metainfo_content_type_id_fkey" FOREIGN KEY > (content_type_id) REFERENCES django_content_type(id) DEFERRABLE > INITIALLY DEFERRED > =A0 =A0"portal_metainfo_owner_type_id_fkey" FOREIGN KEY (slot_type_id) > REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED > Has OIDs: no > > > > As far as I understand the explain, it fetches 68505 rows, matches > them with core_base and then tries to sort them? AFAIK it would > probably be much more effective to just find the records in accessor > via core_accessor_dated_idx and then lookup the core_base table? But > for some reason it doesn't want to? > > I ran analyze, vacuum and reindex but nothing helped. Queries just eat > all the I/O and block. There is a huge difference between cached and > non-cached queries, like 50.000 to 50 ms. > > Help! :) Please send the output of EXPLAIN ANALYZE for this query. ...Robert