Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B518D6336F8 for ; Tue, 3 Mar 2009 13:05:19 -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 11024-10 for ; Tue, 3 Mar 2009 13:05:14 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f168.google.com (mail-fx0-f168.google.com [209.85.220.168]) by mail.postgresql.org (Postfix) with ESMTP id 6168B6334FA for ; Tue, 3 Mar 2009 13:05:12 -0400 (AST) Received: by fxm12 with SMTP id 12so3482831fxm.19 for ; Tue, 03 Mar 2009 09:05:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type:content-transfer-encoding; bh=EmBo1nkP9HrJ8NjAyCCZWx/aMIJCwZYRSNEM3e0tNmE=; b=gxZgfFa+ZQVvb8HxPgOpVRMAyGI0vEB2s7FoNzlSrxOvrcSPVBjTALLJN4pB+oyZ83 6VEvezQOOIOt0VJE3JX4xWxH0xg66iKAPyHp/cVEVehXCXmeXopFJb1nEMvhWQkqP/Wb Fg+c/PHgyfFeQFw5DkGgGANQGZ9KdPHBgEdcw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type :content-transfer-encoding; b=AE0lRMJbSQPMZhsg7uUk/julEcoJ2+mywsFqC4PYV2ldpBMrNLV+nrcrslXXjbUYPW wqFrhYYff6xCCzgHtcqj54LQfdji2J6CrlY8uccpO2LqcJCGfPxberc842/LotBIFjge O/O8kKb4ifQ7PgzN6Bgi8zc0xCKRYd57CKGAI= MIME-Version: 1.0 Received: by 10.223.122.15 with SMTP id j15mr7220796far.74.1236099910750; Tue, 03 Mar 2009 09:05:10 -0800 (PST) Date: Tue, 3 Mar 2009 18:05:10 +0100 Message-ID: Subject: Problems with ordering (can't force query planner to use an index) From: Sebastjan Trepca To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=UTF-8 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/24 X-Sequence-Number: 32946 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", "core_base"."date_posted", "core_base"."date_modified", "core_base"."date_expires", "core_base"."publish_state", "core_base"."location", "core_base"."location_x", "core_base"."location_y", "core_base"."raw", "core_base"."author_id", "core_base"."excerpt", "core_base"."state_id", "core_base"."country_id", "core_base"."language", "core_base"."_identifier", "core_base"."slot_url", "core_base"."source_id", "core_base"."source_content_type_id", "core_base"."source_type", "core_base"."source_value", "core_base"."source_title", "core_base"."direct_to_source", "core_base"."comment_count", "core_base"."public" FROM "core_accessor" INNER JOIN core_base AS core_base ON core_base.content_type_id =3D core_accessor.content_type_id AND core_base.object_id =3D core_accessor.object_id WHERE (("core_accessor"."slot_type_id" =3D 119 AND "core_accessor"."slot_id" =3D 472 AND "core_accessor"."label" =3D E'' AND "core_accessor"."publish_state" >=3D 60 AND "core_accessor"."role" IN (0) AND "core_accessor"."user_id" =3D 0)) order by core_accessor.date_posted, core_accessor.nooximity LIMIT 5 ; QUERY PLAN ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------- Limit (cost=3D31930.65..31930.66 rows=3D5 width=3D860) (actual time=3D711.924..711.927 rows=3D5 loops=3D1) -> Sort (cost=3D31930.65..31937.80 rows=3D2861 width=3D860) (actual time=3D711.923..711.923 rows=3D5 loops=3D1) Sort Key: core_accessor.date_posted, core_accessor.nooximity Sort Method: top-N heapsort Memory: 31kB -> Nested Loop (cost=3D0.00..31883.13 rows=3D2861 width=3D860) (actual time=3D0.089..543.497 rows=3D68505 loops=3D1) -> Index Scan using core_accessor_fresh_idx on core_accessor (cost=3D0.00..5460.07 rows=3D2970 width=3D92) (actual time=3D0.068..54.921 rows=3D69312 loops=3D1) Index Cond: ((slot_id =3D 472) AND (slot_type_id =3D 119) AND (label =3D ''::text) AND (user_id =3D 0) AND (role =3D 0) AND (publish_state >=3D 60)) -> Index 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) Index Cond: ((core_base.object_id =3D core_accessor.object_id) AND (core_base.content_type_id =3D core_accessor.content_type_id)) Total runtime: 712.031 ms (10 rows) noovo-new=3D# select * from pg_stat_user_tables where relname=3D'core_acces= sor'; relid | schemaname | relname | 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 | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze -------+------------+---------------+----------+--------------+----------+-= --------------+-----------+-----------+-----------+---------------+--------= ----+------------+-------------------------------+-----------------+-------= ------------------------+------------------ 51159 | public | core_accessor | 58 | 749773516 | 13785608 | 149165183 | 9566 | 548 | 347 | 206 | 17144303 | 251 | 2009-03-03 07:02:19.733778-06 | | 2009-03-03 06:17:47.784268-06 | (1 row) noovo-new=3D# \d+ core_accessor; Table "public.core_access= or" Column | Type | Modifiers | Description ---------------------------+--------------------------+--------------------= ----------------------------------------+------------- id | bigint | not null default nextval('core_accessor_id_seq'::regclass) | flavor | character varying(32) | | content_type_id | integer | not null | object_id | integer | not null | publish_state | smallint | not null | date_posted | timestamp with time zone | not null | user_id | integer | | slot_id | integer | | slot_type_id | integer | | role | smallint | | ordering | integer | | author_id | integer | | nooximity_old | double precision | default 0.0 | rising | double precision | default 0.0 | label | text | | nooximity | double precision | not null default 1.0 | nooximity_old_date_posted | timestamp with time zone | | nooximity_date_posted | timestamp with time zone | | Indexes: "portal_metainfo_pkey" PRIMARY KEY, btree (id) "portal_metainfo_unique_constr" UNIQUE, btree (content_type_id, object_id, user_id, slot_id, slot_type_id, role, label) CLUSTER "core_accessor_date_idx" btree (date_posted, nooximity) "core_accessor_dated_idx" btree (slot_id, slot_type_id, label, user_id, role, publish_state, date_posted, nooximity) "core_accessor_fresh_idx" btree (slot_id, slot_type_id, label, user_id, role, publish_state) "core_accessor_popularity_idx" btree (nooximity, date_posted) Check constraints: "portal_metainfo_object_id_check" CHECK (object_id >=3D 0) "portal_metainfo_owner_id_check" CHECK (slot_id >=3D 0) Foreign-key constraints: "portal_metainfo_accessor_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED "portal_metainfo_content_type_id_fkey" FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED "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! :) Thanks, Sebastjan