X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 56EE655EAE for ; Mon, 14 Mar 2005 16:50:49 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 83055-06 for ; Mon, 14 Mar 2005 16:50:38 +0000 (GMT) Received: from school.digsys.bg (school.digsys.bg [193.68.6.1]) by svr1.postgresql.org (Postfix) with ESMTP id 9EA4D56634 for ; Mon, 14 Mar 2005 16:50:36 +0000 (GMT) Received: from faith.digsys.bg (sigma.digsys.bg [193.68.6.19]) by school.digsys.bg (8.11.6/8.10.1) with ESMTP id j2EGoWV15917; Mon, 14 Mar 2005 18:50:32 +0200 (EET) Message-ID: <4235C3E9.2080505@faith.digsys.bg> Date: Mon, 14 Mar 2005 19:03:37 +0200 From: Kaloyan Iliev Iliev User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040115 X-Accept-Language: en-us, en, bg MIME-Version: 1.0 To: =?windows-1251?Q?Miroslav_=8Aulc?= , pgsql-performance@postgresql.org Subject: Re: How to read query plan References: <42345D24.8070104@startnet.cz> <4234692E.3050802@arbash-meinel.com> <42347407.2000202@startnet.cz> <42347D5E.9000900@arbash-meinel.com> <42348F54.1000408@startnet.cz> <423493E5.6040203@arbash-meinel.com> <42355249.8090101@startnet.cz> <423556B8.4020500@startnet.cz> <14079.1110817416@sss.pgh.pa.us> <4235BCE8.1090705@startnet.cz> In-Reply-To: <4235BCE8.1090705@startnet.cz> Content-Type: multipart/alternative; boundary="------------050004070400010902040206" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.12 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200503/269 X-Sequence-Number: 10974 This is a multi-part message in MIME format. --------------050004070400010902040206 Content-Type: text/plain; charset=windows-1251; format=flowed Content-Transfer-Encoding: 8bit Hi, I have an idea about your problem. Will it be difficult not to change the entire code but only the queries? You can change type in the Postgres to bool. Then, when select data you can use a CASE..WHEN to return 'Y' or 'N' or even write a little function which accepts bool and returns 'Y' or 'N'. In this case in all your queries you will have to replace the select of bool field with select form the function. Kaloyan Miroslav Šulc wrote: > Tom Lane wrote: > >> Just FYI, I did a quick search-and-replace on your dump to replace >> varchar(1) by "char", which makes the column fixed-width without any >> change in the visible data. This made hardly any difference in the >> join speed though :-(. So that is looking like a dead end. >> >> > I'll try to change the data type to bool but the problem I stand in > front of is that the code expects that SELECTs return 'Y' or 'N' but > what I have found out till now is that PostgreSQL returns 't' or 'f' > for bool data. I think about some solution but they use CPU :-( > >> John's idea about re-joining to the main table to pick up the bulk of >> its fields only after joining to the sub-tables might work. >> >> > I'll try that. It seems it could work. > >> regards, tom lane >> >> > Miroslav > >------------------------------------------------------------------------ > > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > --------------050004070400010902040206 Content-Type: text/html; charset=windows-1251 Content-Transfer-Encoding: 8bit Hi,

I have an idea about your problem. Will it be difficult not to change the entire code but only the queries? You can change type in the Postgres to bool. Then, when select data you can use a CASE..WHEN to return 'Y' or 'N' or even write a little function which accepts bool and returns 'Y' or 'N'. In this case in all your queries you will have to replace the select of bool field with select form the function.

Kaloyan

Miroslav Šulc wrote:
Tom Lane wrote:

Just FYI, I did a quick search-and-replace on your dump to replace
varchar(1) by "char", which makes the column fixed-width without any
change in the visible data.  This made hardly any difference in the
join speed though :-(.  So that is looking like a dead end.
 

I'll try to change the data type to bool but the problem I stand in front of is that the code expects that SELECTs return 'Y' or 'N' but what I have found out till now is that PostgreSQL returns 't' or 'f' for bool data. I think about some solution but they use CPU :-(

John's idea about re-joining to the main table to pick up the bulk of
its fields only after joining to the sub-tables might work.
 

I'll try that. It seems it could work.

            regards, tom lane
 

Miroslav

---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
--------------050004070400010902040206--