X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A8089D1D98A for ; Mon, 19 Apr 2004 23:29:19 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 12981-02 for ; Mon, 19 Apr 2004 23:29:21 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id AF898D1B564 for ; Mon, 19 Apr 2004 23:29:14 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id A5EAB34288; Mon, 19 Apr 2004 23:29:18 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id A32F9341DD for ; Mon, 19 Apr 2004 23:29:18 -0300 (ADT) X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.3) with LMTP; Mon, 19 Apr 2004 08:30:38 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id CF48F35BBD for ; Mon, 19 Apr 2004 08:30:27 -0300 (ADT) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Mon, 19 Apr 2004 08:30:27 -0300 (ADT) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.3) with LMTP; Mon, 19 Apr 2004 08:30:15 -0300 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 9C69AD1D08C for ; Mon, 19 Apr 2004 08:30:14 -0300 (ADT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 69136-07 for ; Mon, 19 Apr 2004 08:30:16 -0300 (ADT) X-Received: from minnie.omroep.nl (minnie.omroep.nl [145.58.30.4]) by svr1.postgresql.org (Postfix) with ESMTP id BA04FD1BCB8 for ; Mon, 19 Apr 2004 08:30:10 -0300 (ADT) X-Received: from tron.omroep.nl (tron.omroep.nl [145.58.31.20]) by minnie.omroep.nl (Postfix) with ESMTP id AB8271CF3E15 for ; Mon, 19 Apr 2004 13:30:13 +0200 (CDT) X-Received: from michiel.omroep.nl (michiel.omroep.nl [145.58.67.10]) by tron.omroep.nl (Postfix) with ESMTP id 9E8CE10056BD for ; Mon, 19 Apr 2004 13:30:13 +0200 (CEST) X-Received: from michiel by michiel.omroep.nl with local (Exim 3.35 #1 (Debian)) id 1BFWyb-0008UY-00 for ; Mon, 19 Apr 2004 13:30:13 +0200 Date: Mon, 19 Apr 2004 13:30:13 +0200 From: Michiel Meeuwissen To: performance@postgresql.org Subject: order by index, and inheritance Message-ID: <20040419113013.GA32186@michiel.omroep.nl> Reply-To: Michiel Meeuwissen Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Organization: Publieke Omroep []() User-Agent: Mutt/1.5.5.1+cvs20040105i X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-DCC: EATSERVER: ganymede.hub.org 1166; IP=ok Body=1 Fuz1=1 Fuz2=1 X-Spam-Pyzor: Reported 0 times. ReSent-Date: Mon, 19 Apr 2004 23:29:13 -0300 (ADT) Resent-From: "Marc G. Fournier" Resent-To: pgsql-performance@postgresql.org ReSent-Subject: order by index, and inheritance ReSent-Message-ID: <20040419232913.V780@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/283 X-Sequence-Number: 6583 I have a query which performs not so well: SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20; costs nearly a minute. The table contains over 300 000 records. The table has two extensions, which are (a the moment) nearly empty, but have something to do with this, because: SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20; performs ok (8ms). The query plan is then as I would expect: media=# explain SELECT * FROM only mm_mediasources ORDER BY number DESC LIMIT 20; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..8.36 rows=20 width=105) -> Index Scan Backward using mediasource_object on mm_mediasources (cost=0.00..114641.05 rows=274318 width=105) The query plan of the original query, without 'only' does table scans: media=# explain SELECT * FROM mm_mediasources ORDER BY number DESC LIMIT 20; QUERY PLAN -------------------------------------------------------------------------------------------------------- Limit (cost=47248.70..47248.75 rows=20 width=105) -> Sort (cost=47248.70..47934.52 rows=274328 width=105) Sort Key: public.mm_mediasources.number -> Result (cost=0.00..8364.28 rows=274328 width=105) -> Append (cost=0.00..8364.28 rows=274328 width=105) -> Seq Scan on mm_mediasources (cost=0.00..8362.18 rows=274318 width=105) -> Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84) -> Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.09 rows=9 width=89) and presumably because if that performs so lousy. Simply selecting on a number does work fast: media=# explain SELECT * FROM mm_mediasources where number = 606973 ; QUERY PLAN -------------------------------------------------------------------------------------------------------- Result (cost=0.00..6.13 rows=4 width=105) -> Append (cost=0.00..6.13 rows=4 width=105) -> Index Scan using mediasource_object on mm_mediasources (cost=0.00..4.00 rows=2 width=105) Index Cond: (number = 606973) -> Seq Scan on mm_audiosources mm_mediasources (cost=0.00..1.01 rows=1 width=84) Filter: (number = 606973) -> Seq Scan on mm_videosources mm_mediasources (cost=0.00..1.11 rows=1 width=89) Filter: (number = 606973) (3ms) I suppose seq scans are used on the extensions because they contain so few records. All tables have index on number. How do I force it to use them also when I use order by? I use psql 7.3.2 Michiel -- Michiel Meeuwissen | Mediapark C101 Hilversum | +31 (0)35 6772979 | I hate computers nl_NL eo_XX en_US | mihxil' | [] () |