Re: [GENERAL] [Skytools-users] WAL Shipping + checkpoint
On Tue, 2009-09-01 at 13:14 +1000, Yaroslav Tykhiy wrote: > Could you detail your solution please, if any? I've seen frozen > pg_controldata output on my standby server for ages and attributed > that to the ancient version of pgsql (8.0.x) I'm stuck with. It won't ever work before 8.2 -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connecting to Teradata via Postgresql
On Sun, 2009-08-30 at 23:21 +0800, Ow Mun Heng wrote: > Anyone here has a teradata box ? Are you able to connect to it from withing > postgresql? > > I would like to pull 1or 2 tables from the box (sync) and was wondering if > there's anyway to do that w/o using dbi-link. > > I actually am trying dbi-link but it seem as though it doesn't support > DBD::Teradata. > > Based on the error descriptions, and some googling, seems like Teradata.pm > does not support the "column_info" DBI method of getting the column > descriptions and such. > > is there any other methods available? The open source version of DBD::Teradata is a cut down version and I doubt this would be the only problem with it. Have you tried the licenced version? If that doesn't work then probably best to report it to the authors. Otherwise, I'd do a bteq export in CSV format and reload using COPY. It's likely to be faster too. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Eclipse jdbc postgresql
--- On Tue, 1/9/09, Sheepjxx wrote: > If I want to use postgres with jdbc , > I have already download jdbc, do I need extra option > for compile postgres?--with-java?do I need change > postgres.conf? > No, you just need the postgres jdbc driver (jdbc.postgresql.org) in your classpath. Glyn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Aggregate function with subquery in 8.3 and 8.4.
On Mon, Aug 31, 2009 at 04:02:43PM -0700, Sheng Cheng wrote: > I though the following query would give me the same results in 8.4.0 and > 8.3.1. It should give the same results! This looks like a bug in 8.4 to me, in an attempt to optimize things it's pulling the CASE out from inside the inner select and this is changing the semantics of the query. Doing an EXPLAIN in 8.4 gives the following: GroupAggregate (cost=181.86..387.73 rows=200 width=64) -> Merge Left Join (cost=181.86..341.83 rows=8580 width=64) Merge Cond: (t1.f1 = (CASE WHEN (t2.f1 = '111'::text) THEN '111'::text ELSE t2.f1 END)) -> Sort (cost=90.93..94.20 rows=1310 width=32) Sort Key: t1.f1 -> Seq Scan on t1 (cost=0.00..23.10 rows=1310 width=32) -> Sort (cost=90.93..94.20 rows=1310 width=64) Sort Key: (CASE WHEN (t2.f1 = '111'::text) THEN '111'::text ELSE t2.f1 END) -> Seq Scan on t2 (cost=0.00..23.10 rows=1310 width=64) While in 8.3 I get: GroupAggregate (cost=198.23..378.88 rows=200 width=64) -> Merge Left Join (cost=198.23..333.48 rows=8580 width=64) Merge Cond: (t1.f1 = ts.f1) -> Sort (cost=90.93..94.20 rows=1310 width=32) Sort Key: t1.f1 -> Seq Scan on t1 (cost=0.00..23.10 rows=1310 width=32) -> Sort (cost=107.30..110.58 rows=1310 width=64) Sort Key: ts.f1 -> Subquery Scan ts (cost=0.00..39.48 rows=1310 width=64) -> Seq Scan on t2 (cost=0.00..26.38 rows=1310 width=32) Notice that the "Merge Cond" is working on the CASE expression in 8.4. This is too late and is breaking things. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Aggregate function with subquery in 8.3 and 8.4.
On Tue, Sep 01, 2009 at 10:05:44AM +0100, Sam Mason wrote: > On Mon, Aug 31, 2009 at 04:02:43PM -0700, Sheng Cheng wrote: > > I though the following query would give me the same results in 8.4.0 and > > 8.3.1. > > It should give the same results! This looks like a bug in 8.4 to me I've just noticed this was (invisibly to me) cross-posted to -bugs as well. Probably best to reply there. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_ctl with unix domain socket?
Hello, I have created a fresh cluster with initdb -D /some/path/pgtest I can start postgres to run on unix domain socket serving this cluster with: postgres -D /some/path/pgtest -h '' -k /some/path/pgtest But I'd like to use pg_ctl instead, in order to have clean control: PGPORT=/some/path/pgtest pg_ctl -D/some/path/pgtest -l postgreslog start Any hints how to use pg_ctl to start/stop postgresql on a unix domain socket? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No buffer space available
I am using machine which is exceeding more than 100 connection from my application(iview-syslog server) to postgres. I have updated configuration for postgres in postgres.conf for max connection. But then also it is giving me the error as No buffer space available. And it is happening with only iviewdb named database. Other databases are working well and good. After searching on net i found the limitation is from windows itself. and i have fixed the error with the following registry hack. Regards, Narendra Shah. No buffer space available Fix Editting the registry is not for beginners, if you don't know what you're doing I suggest you don't try this, basically it's use at your own risk. Anytime you want to edit the registry it is a good idea to back it up first. For information on how to backup and restore the registry in all versions of Windows click here. If you are using Windows 95/98/Me follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP once there, look for an entry called MaxConnections. If it exists highlight it by clicking it and then right click it and select modify. Increase the value (recommended value is to double the current value). If the MaxConnections entry does not exist you must create it. To do this, right click in the right pane and choose new from the menu and select String Value. Give it the name MaxConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). If you are running Windows NT/2000/XP follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters once there, you must create the entry TcpNumConnections. To do this, right click in the right pane and choose new from the menu and select DWORD Value. Give it the name TcpNumConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). -- View this message in context: http://www.nabble.com/No-buffer-space-available-tp9335358p25238999.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ctl with unix domain socket?
On Tue, Sep 1, 2009 at 4:58 PM, Josef Wolf wrote: > Hello, > > I have created a fresh cluster with > > initdb -D /some/path/pgtest > > I can start postgres to run on unix domain socket serving this cluster > with: > > postgres -D /some/path/pgtest -h '' -k /some/path/pgtest > > But I'd like to use pg_ctl instead, in order to have clean control: > > PGPORT=/some/path/pgtest pg_ctl -D/some/path/pgtest -l postgreslog start > > PGPORT should be the port number on which the Postgresql server is running(5432 is default), and PGDATA=some/path/pgtest . To start the postgresql server using pg_ctl you can use, pg_ctl -D PGDATA start to stop , pg_ctl -D PGDATA stop Abbas. > Any hints how to use pg_ctl to start/stop postgresql on a unix domain > socket? > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] pg_ctl with unix domain socket?
On Tue, Sep 01, 2009 at 05:28:46PM +0530, Abbas wrote: > On Tue, Sep 1, 2009 at 4:58 PM, Josef Wolf wrote: Thanks for your fast response, Abbas! > > I have created a fresh cluster with > > initdb -D /some/path/pgtest > > PGPORT=/some/path/pgtest pg_ctl -D/some/path/pgtest -l postgreslog start > > PGPORT should be the port number on which the Postgresql server is > running(5432 is default), and PGDATA=some/path/pgtest . Yeah, I see. But I do not want it to bind to network socket. I want it to bind to a unix domain socket _only_. While all other postgres commands accept an absolute path as port specification, meaning to use a unix domain socket, pg_ctl seems to behave differently. e.g: I can start postgres on a unix domain socket _only_ with following parameters: postgres -D /some/path/pgtest -h '' -k /some/path/pgtest But pg_ctl don't seem to have an option to do the same. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Query and the number of row result
On 31/08/2009 18:00, Tim Landscheidt wrote: > Presuming that you are talking about a function written in > PL/pgSQL, you will have to count them yourself or issue a > second query "SELECT COUNT(*) FROM [...]". For the special > case that you want to find out whether no row at all was > found, you can look at "IF (NOT) FOUND". In pl/pgsql you can also issue a GET DIAGNOSTICS command which gets the row count: http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS ...although it is still has to be done as a second query, as in your suggestions above. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Keys and indexes
Does indexing working for foreignkeys column automaticaly? Or i need create index manually. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ctl with unix domain socket?
Josef Wolf wrote: > > PGPORT should be the port number on which the Postgresql server is > > running(5432 is default), and PGDATA=some/path/pgtest . > > Yeah, I see. But I do not want it to bind to network socket. I want it to > bind to a unix domain socket _only_. Typically this is done by setting listen_addresses (in postgresql.conf) to an empty list, as explained here: http://www.postgresql.org/docs/8.4/static/runtime-config-connection.html#RUNT IME-CONFIG-CONNECTION-SETTINGS Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ctl with unix domain socket?
On Tuesday 01 September 2009 4:28:22 am Josef Wolf wrote: > Hello, > > I have created a fresh cluster with > >initdb -D /some/path/pgtest > > I can start postgres to run on unix domain socket serving this cluster > with: > >postgres -D /some/path/pgtest -h '' -k /some/path/pgtest > > But I'd like to use pg_ctl instead, in order to have clean control: > >PGPORT=/some/path/pgtest pg_ctl -D/some/path/pgtest -l postgreslog start > > > Any hints how to use pg_ctl to start/stop postgresql on a unix domain > socket? 1) In postgresql.conf make listen_addresses empty per instructions: http://www.postgresql.org/docs/8.4/interactive/runtime-config-connection.html#GUC-LISTEN-ADDRESSES listen_addresses (string) Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications. The value takes the form of a comma-separated list of host names and/or numeric IP addresses. The special entry * corresponds to all available IP interfaces. If the list is empty, the server does not listen on any IP interface at all, in which case only Unix-domain sockets can be used to connect to it. The default value is localhost, which allows only local "loopback" connections to be made. This parameter can only be set at server start. 2) Use the -o switch to pass commands to postgres. See below: http://www.postgresql.org/docs/8.4/interactive/app-pg-ctl.html -o options Specifies options to be passed directly to the postgres command. The options are usually surrounded by single or double quotes to ensure that they are passed through as a group. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Keys and indexes
On Sep 1, 2009, at 7:37 , Alexandr Varlamov wrote: Does indexing working for foreignkeys column automaticaly? No. Or i need create index manually. Yes. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_ctl with unix domain socket?
On Tue, Sep 01, 2009 at 06:34:21AM -0700, Adrian Klaver wrote: > On Tuesday 01 September 2009 4:28:22 am Josef Wolf wrote: Thanks for your help, Adrian! [ ... ] > > Any hints how to use pg_ctl to start/stop postgresql on a unix domain > > socket? > > 1) In postgresql.conf make listen_addresses empty per instructions: I could get it running with putting listen_addresses='' in postgresql.conf and then running pg_ctl -Ddb -o "-h '' -k `pwd`/db" -l postgreslog start pg_ctl -Ddb -o "-h '' -k `pwd`/db" -l postgreslog stop Thanks to all who helped -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Get closest numbers between intervals
Hi, I have one table that has this structure: city_code, start_ip, end_ip I´m searching for the city that its between two ip´s. But, i would like to get the nearest´s cities too. Any ideas how can i search for the city proximity? thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Any former bugs that loose duplicates after a sort
The is someone in the MS-Access community that is seeing duplicated records (differing by a serial id) disappear with sorting the list by the unique serial id. Were there any older version of Postgres that did this? http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1876017&page=0&view=&sb=5&o=&fpart=1&vc=1&PHPSESSID= -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Any former bugs that loose duplicates after a sort
- "Richard Broersma" wrote: > The is someone in the MS-Access community that is seeing duplicated > records (differing by a serial id) disappear with sorting the list by > the unique serial id. Were there any older version of Postgres that > did this? > > http://www.utteraccess.com/forums/showflat.php?Cat=&Number=1876017&page=0&view=&sb=5&o=&fpart=1&vc=1&PHPSESSID= > > -- > Regards, > Richard Broersma Jr. > My reading of the thread is that he is seeing duplicate records that include duplicates of the serial id. The dataset is derived from a join of tables and Access queries. My guess is that the query he built is not doing what he thinks it is. This would be especially true if he used the GUI query builder, it tends to build some funky queries (especially when run against non-Jet datasources). When querying Postgres from Access I usually use pass through queries to keep Access from mucking them up to much. Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to use multiple schema's
Hi, I have multiple applications, i want to create separate schema for each of the application. I dont want to change search path, since have only one user. I went through couple of post's talking about some patch related to that. It seems that patch got rejected. Any help is appreciated. Thanks and Regards, Himanshu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get closest numbers between intervals
xaviergxf writes: > city_code, start_ip, end_ip > > I´m searching for the city that its between two ip´s. But, i would > like to get the nearest´s cities too. I'd have a look at ip4r rather than having both the start and end ip there. The GiST index on ip4r allow for quick lookup: SELECT * FROM ... WHERE iprange >>= '1.2.3.4'::ip4; http://pgfoundry.org/projects/ip4r/ > Any ideas how can i search for the city proximity? I'd have a look at earthdistance contrib, then maybe PostGIS: http://www.postgresql.org/docs/8.4/static/earthdistance.html Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No buffer space available
--- On Tue, 9/1/09, Narendra Shah wrote: From: Narendra Shah Subject: Re: [GENERAL] No buffer space available To: pgsql-general@postgresql.org Date: Tuesday, September 1, 2009, 11:53 AM I am using machine which is exceeding more than 100 connection from my application(iview-syslog server) to postgres. I have updated configuration for postgres in postgres.conf for max connection. But then also it is giving me the error as No buffer space available. And it is happening with only iviewdb named database. Other databases are working well and good. After searching on net i found the limitation is from windows itself. and i have fixed the error with the following registry hack. Regards, Narendra Shah. No buffer space available Fix Editting the registry is not for beginners, if you don't know what you're doing I suggest you don't try this, basically it's use at your own risk. Anytime you want to edit the registry it is a good idea to back it up first. For information on how to backup and restore the registry in all versions of Windows click here. If you are using Windows 95/98/Me follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP once there, look for an entry called MaxConnections. If it exists highlight it by clicking it and then right click it and select modify. Increase the value (recommended value is to double the current value). If the MaxConnections entry does not exist you must create it. To do this, right click in the right pane and choose new from the menu and select String Value. Give it the name MaxConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). If you are running Windows NT/2000/XP follow these steps: First step is to launch the registry editor. To do this go to Start, Run and type regedit. In the left pane navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters once there, you must create the entry TcpNumConnections. To do this, right click in the right pane and choose new from the menu and select DWORD Value. Give it the name TcpNumConnections. Then right click it and select modify and enter a value of 200. Restart your computer, if all goes well then you fixed the problem, if not, revert the changes by restoring the registry. (You may have to reboot to safe mode to do this). -- View this message in context: http://www.nabble.com/No-buffer-space-available-tp9335358p25238999.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general IMHO you have to use pgpool to handle many connections Lennin Caro Pérez Usuario:GNU/LINUX PHP Developer PostgreSQL DBA Oracle DBA Linux counter id 474393
Re: [GENERAL] how to use multiple schema's
On Tue, Sep 1, 2009 at 2:58 PM, Himanshu Gupta wrote: > Hi, > > I have multiple applications, i want to create separate schema for each of > the application. I dont want to change search path, since have only one > user. I went through couple of post's talking about some patch related to > that. It seems that patch got rejected. Any help is appreciated. I'm not sure how you want this to happen. Do you want a single user to see ALL the schemas at once? Or do you want to set the search path each time you connect? Is there a reason for having multiple apps hit multiple schemas but use only one account? Multiple accounts (one for each app) would certainly make things more manageable. But mainly I'm just trying to get a grip on how you're trying to get this to work. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enum
Is there a query i can use to get the fields of an Enum, just in case someone needs it for the client application. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enum
Xai wrote: Is there a query i can use to get the fields of an Enum, just in case someone needs it for the client application. select e.enumlabel from pg_enum as e join pg_type as t on (t.typtype='e' and e.enumtypeid=t.typbasetype) where t.typname = $1 order by e.enumtypid; I think. or something close to that. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] No buffer space available
Hi Lennnin, Thanks a lot for providing your kind help. But after tweaking registry, yet problem not solved. On yesterday evening when i check again. Same problem occured, what i have done is i have queried one table. Then after Database again giving me the same error. Requesting you to please provide solution to this. That table might contains record more than 5 lakh. And that table is not indexed. So is that a problem ? Regards, Narendra Shah, Cyberaom, Elitecore Technolgies Ltd, Ahmedabad Lennin Caro wrote: > > > IMHO you have to use pgpool to handle many connections > > > Lennin Caro Pérez > > Usuario:GNU/LINUX > > PHP Developer > > PostgreSQL DBA > > Oracle DBA > > Linux counter id 474393 > > > > -- View this message in context: http://www.nabble.com/No-buffer-space-available-tp9335358p25251894.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Join efficiency
I'm sorry for my mistake, the postgre version is:
test_db=# SELECT version();
version
---
PostgreSQL 8.1.8 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)
- Original Message -
From: "tanjunhua"
To:
Sent: Wednesday, September 02, 2009 2:31 PM
Subject: [GENERAL] Join efficiency
Hello, everybody.
In my project, I have a select syntax to get record summary between three
tables. one of them is tab_main consist of 46 columns(with 27797
records), another is tab_user consist of 32 columns(with 3 records) and
the last one is tab_property consist of 117 columns(with 30541 records). I
have the trouble that it cost me a lot of time when execute the select
syntax. the following is the select syntax and analyze result.
table structure:
tab_main(id, uid, status, bpassword, realdelflag, delflag, kind, ...)
tab_user(uid, printauth, bprtpermit, ...)
tab_property(id, mode, ...)
1. select syntax:
EXPLAIN ANALYZE SELECT count(Id) FROM (SELECT DISTINCT t1.Id AS Id FROM
tab_main t1, tab_user t2, tab_property t3 WHERE (t1.uid = 2 AND t1.status
>= 21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind= 1 OR
(t1.kind = 0 AND (t1.delflag <> 0 OR (t2.uid = 2 AND (t2.printauth = 2 OR
t2.printauth = 3) AND t2.bprtpermit = 0 AND t3.id = t1.id AND (t3.mode = 0
OR t3.mode = 1))) subt0;
2. analyze result:
Aggregate (cost=19129.93..19129.94 rows=1 width=4) (actual
time=10661.656..10661.658 rows=1 loops=1)
-> Unique (cost=18672.11..19129.92 rows=1 width=4) (actual
time=8288.446..10661.586 rows=5 loops=1)
-> Sort (cost=18672.11..18901.01 rows=91562 width=4) (actual
time=8288.440..9532.507 rows=458115 loops=1)
Sort Key: t1.id
-> Nested Loop (cost=1550.00..10341.45 rows=91562 width=4)
(actual time=5.002..4724.436 rows=458115 loops=1)
Join Filter: (("inner".jobkind = 1) OR
(("inner".jobkind = 0) AND (("inner".delflag <> 0) OR (("inner".uid = 2)
AND (("inner".printright = 2) OR ("inner".printright = 3)) AND
("inner".print_permitcolorprint = 0) AND ("outer".id = "inner".id) AND
(("outer".colormode = 0) OR ("outer".colormode = 1))
-> Seq Scan on job_p t3 (cost=0.00..4668.41
rows=30541 width=8) (actual time=0.023..170.619 rows=30541 loops=1)
-> Materialize (cost=1550.00..1550.03 rows=3
width=24) (actual time=0.002..0.055 rows=21 loops=30541)
-> Nested Loop (cost=0.00..1550.00 rows=3
width=24) (actual time=4.949..149.081 rows=21 loops=1)
-> Seq Scan on job_ctl t1
(cost=0.00..1548.94 rows=1 width=12) (actual time=4.906..148.794 rows=7
loops=1)
Filter: ((uid = 2) AND (jobsts >=
21) AND (pinflag = 0) AND (realdelflag = 0))
-> Seq Scan on users t2 (cost=0.00..1.03
rows=3 width=12) (actual time=0.007..0.016 rows=3 loops=7)
Total runtime: 10696.630 ms
(13 rows)
could anyone explain the result of analyze and give me some idea to speed
up the select? looking forward your response.
best wishes.
winsea
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Join efficiency
Hello, everybody.
In my project, I have a select syntax to get record summary between three
tables. one of them is tab_main consist of 46 columns(with 27797 records),
another is tab_user consist of 32 columns(with 3 records) and the last one
is tab_property consist of 117 columns(with 30541 records). I have the
trouble that it cost me a lot of time when execute the select syntax. the
following is the select syntax and analyze result.
table structure:
tab_main(id, uid, status, bpassword, realdelflag, delflag, kind, ...)
tab_user(uid, printauth, bprtpermit, ...)
tab_property(id, mode, ...)
1. select syntax:
EXPLAIN ANALYZE SELECT count(Id) FROM (SELECT DISTINCT t1.Id AS Id FROM
tab_main t1, tab_user t2, tab_property t3 WHERE (t1.uid = 2 AND t1.status >=
21 AND t1.bpassword = 0 AND t1.realdelflag = 0 AND (t1.kind= 1 OR (t1.kind
= 0 AND (t1.delflag <> 0 OR (t2.uid = 2 AND (t2.printauth = 2 OR
t2.printauth = 3) AND t2.bprtpermit = 0 AND t3.id = t1.id AND (t3.mode = 0
OR t3.mode = 1))) subt0;
2. analyze result:
Aggregate (cost=19129.93..19129.94 rows=1 width=4) (actual
time=10661.656..10661.658 rows=1 loops=1)
-> Unique (cost=18672.11..19129.92 rows=1 width=4) (actual
time=8288.446..10661.586 rows=5 loops=1)
-> Sort (cost=18672.11..18901.01 rows=91562 width=4) (actual
time=8288.440..9532.507 rows=458115 loops=1)
Sort Key: t1.id
-> Nested Loop (cost=1550.00..10341.45 rows=91562 width=4)
(actual time=5.002..4724.436 rows=458115 loops=1)
Join Filter: (("inner".jobkind = 1) OR
(("inner".jobkind = 0) AND (("inner".delflag <> 0) OR (("inner".uid = 2) AND
(("inner".printright = 2) OR ("inner".printright = 3)) AND
("inner".print_permitcolorprint = 0) AND ("outer".id = "inner".id) AND
(("outer".colormode = 0) OR ("outer".colormode = 1))
-> Seq Scan on job_p t3 (cost=0.00..4668.41
rows=30541 width=8) (actual time=0.023..170.619 rows=30541 loops=1)
-> Materialize (cost=1550.00..1550.03 rows=3
width=24) (actual time=0.002..0.055 rows=21 loops=30541)
-> Nested Loop (cost=0.00..1550.00 rows=3
width=24) (actual time=4.949..149.081 rows=21 loops=1)
-> Seq Scan on job_ctl t1
(cost=0.00..1548.94 rows=1 width=12) (actual time=4.906..148.794 rows=7
loops=1)
Filter: ((uid = 2) AND (jobsts >= 21)
AND (pinflag = 0) AND (realdelflag = 0))
-> Seq Scan on users t2 (cost=0.00..1.03
rows=3 width=12) (actual time=0.007..0.016 rows=3 loops=7)
Total runtime: 10696.630 ms
(13 rows)
could anyone explain the result of analyze and give me some idea to speed up
the select? looking forward your response.
best wishes.
winsea
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
