Received: from mblox.com (unknown [213.155.153.59]) by postgresql.org (Postfix) with ESMTP id 8C9CB475EC3 for ; Mon, 8 Apr 2002 10:43:10 -0400 (EDT) Received: from salamander (salamander.mblox.com [::ffff:192.168.2.102]) (AUTH: LOGIN ian.cass) by aerosmith with esmtp; Mon, 08 Apr 2002 14:43:44 +0000 Message-ID: <00c401c1df0b$893ee7c0$6602a8c0@salamander> From: "Ian Cass" To: "Christopher Kings-Lynne" , pgsql-sql@postgresql.org References: <002001c1def9$e0fcc750$6602a8c0@salamander> <003401c1df0a$523bead0$0200a8c0@SOL> Subject: Re: JOINS and non use of indexes Date: Mon, 8 Apr 2002 15:41:48 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Archive-Number: 200204/86 X-Sequence-Number: 7351 You're right. But I do this.... my $sql = "create table messages_$month () inherits (messages)"; ... my $sql = "create unique index messages_" . $month . "_ix1 on messages_$month using btree (host, qos_id)"; ... my $sql = "create index messages_" . $month . "_ix2 on messages_$month using btree (client_id, user_name)"; ... Similar thing for my statusinds tables too. -- Ian Cass ----- Original Message ----- From: "Christopher Kings-Lynne" To: "Ian Cass" ; Sent: Monday, April 08, 2002 3:33 PM Subject: Re: [SQL] JOINS and non use of indexes > I haven't had a good look, but just remember that indexes on a table in > postgres are NOT inherited by its children. You cannot define a unique > index over a column that is inherited - it will be unique for the table you > define it on only. Hence, you may not actually have indexes on those > inherited tables, and therefore they cannot be used... > > Chris > > > Explain plan.... > > Limit (cost=0.00..35.06 rows=5 width=620) > > -> Nested Loop (cost=0.00..30986063.51 rows=4418898 width=620) > > -> Append (cost=0.00..441.93 rows=111 width=496) > > -> Seq Scan on messages (cost=0.00..0.00 rows=1 width=496) > > -> Index Scan using messages_200203_ix2 on messages_200203 messages > > (cost=0.00..272.61 rows=68 width=383) > > -> Index Scan using messages_200204_ix2 on messages_200204 messages > > (cost=0.00..169.32 rows=42 width=384) > > -> Append (cost=0.00..180413.11 rows=7996912 width=124) > > -> Seq Scan on statusinds (cost=0.00..0.00 rows=1 width=124) > > -> Seq Scan on statusinds_200203 statusinds (cost=0.00..142835.73 > > rows=6292073 width=71) > > -> Seq Scan on statusinds_200204 statusinds (cost=0.00..37577.38 > > rows=1704838 width=65) > > > > (tables_YYYYMM are inherited) > > > > -- > > Ian Cass > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > >