Received: from ucs.co.za (ucs.co.za [196.23.43.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e94AN0G33291 for ; Wed, 4 Oct 2000 06:23:01 -0400 (EDT) Received: from ucspost.ucs.co.za (IDENT:root@mailgw1.ucs.co.za [196.23.43.254]) by ucs.co.za (8.9.3/8.9.3) with ESMTP id MAA02061; Wed, 4 Oct 2000 12:31:09 +0200 Received: from jhb.ucs.co.za (IDENT:root@jhb.ucs.co.za [172.31.1.73]) by ucspost.ucs.co.za (8.9.3/8.7.3) with ESMTP id MAA26382; Wed, 4 Oct 2000 12:22:33 +0200 Received: from ucs.co.za (IDENT:gaf@pc141.ucs.co.za [172.31.1.141]) by jhb.ucs.co.za (8.9.3/8.8.7) with ESMTP id MAA02139; Wed, 4 Oct 2000 12:28:35 +0200 Message-ID: <39DB056E.54383E92@ucs.co.za> Date: Wed, 04 Oct 2000 12:24:47 +0200 From: Grant Finnemore X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Emils Klotins CC: pgsql-sql@hub.org Subject: Re: Q: performance on some selects (7.0.2)? References: <39DB1CFB.10000.5BA3CB0@localhost> Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from base64 to 8bit by hub.org id e94AN4G33309 X-Archive-Number: 200010/52 Emils, There is no index on articles.id (should it not be a primary key?) Regards, Grant Emils Klotins wrote: > A typical query runs like this: > > SELECT a.id,a.title,c.fullpath,c.section FROM articles > a,articles_groups x,newscategories c WHERE x.articleid=a.id AND > a.categoryid=c.id AND x.groupid='9590' AND a.status=1 AND > timestamp(a.publishdate,a.publishtime)<'now'::datetime ORDER > BY a.createddate desc,a.createdtime desc LIMIT 3 > > Explain says: > > NOTICE: QUERY PLAN: > > Sort (cost=171.93..171.93 rows=1 width=56) > -> Nested Loop (cost=0.00..171.92 rows=1 width=56) > -> Nested Loop (cost=0.00..169.95 rows=1 width=36) > -> Seq Scan on articles_groups x (cost=0.00..12.10 > rows=1 width=4) > -> Seq Scan on articles a (cost=0.00..135.55 rows=636 > width=32) > -> Seq Scan on newscategories c (cost=0.00..1.43 rows=43 > width=20) > > EXPLAIN > > Now, as I understand the thing that slows everything is the Seq > scan on articles. I wonder why should it be that the query can't use > index? > > TIA! > Emils -- > Poorly planned software requires a genius to write it > and a hero to use it. Grant Finnemore BSc(Eng) (mailto:gaf@ucs.co.za) Software Engineer Universal Computer Services Tel (+27)(11)712-1366 PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-5536 20th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421 Johannesburg, South Africa