R: [GENERAL] CachedPlan logs until full disk

[Job]

Dear Tom,

thank you for the reply.
Tonight this problem happened again:

>> CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
>> CachedPlanSource: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
>> SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
>> CachedPlan: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
>> CachedPlanSource: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
>> SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
>> CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used

>This appears to be a fragment of a memory map that would be produced
>in conjunction with an "out of memory" error.  It's difficult to say
>much more than that with only this much information, but clearly you
>need to do something to prevent recurrent out-of-memory errors.

We were automatically bulkling a table that archive system logging.
We used a pg_bulk, but not a create or replace function.

That process often use lots of memory.

Just one question: do you think is it possible to disable that logging sentence?

Thank you!
Francesco


Da: Tom Lane [t...@sss.pgh.pa.us]
Inviato: venerdì 4 novembre 2016 21.24
A: Job
Cc: pgsql-general@postgresql.org
Oggetto: Re: [GENERAL] CachedPlan logs until full disk

Job  writes:
> it is the second time (in two weeks), that  have a very strange Postgresql in 
> a 8.4.22 installation (32 bit still).

You realize, of course, that 8.4.x has been out of support for a couple of
years now.

> Logfile grow up (in few hours) until filling the Whole disk space.
> I can read infinite series of this messages:

> CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
> CachedPlanSource: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
> SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
> CachedPlan: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
> CachedPlanSource: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
> SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
> CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used

This appears to be a fragment of a memory map that would be produced
in conjunction with an "out of memory" error.  It's difficult to say
much more than that with only this much information, but clearly you
need to do something to prevent recurrent out-of-memory errors.

If looking at the map as a whole makes it clear that it's zillions
of CachedPlans that are chewing up most of the memory, then I would
guess that they are getting leaked as a result of constantly replacing
plpgsql functions --- does your application do a lot of
CREATE OR REPLACE FUNCTION commands?  I don't think plpgsql coped
with that very well before 9.1.

regards, tom lane

-- 
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] Moving pg_xlog

[Lucas Possamai]

2016-12-02 17:10 GMT+13:00 Michael Paquier :

> On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson 
> wrote:
> >  Well, while the location of pg_xlog is not currently configurable, on
> Linux system the way to do it is  to:
> >  1. stop PostgreSQL
> >  2. move the pg_xlog directory to a separate partition
> >  3. create a symbolic link to point to the new partition
> >  4. restart PostgreSQL
>
> Similar flow on Windows, just use a junction point for the link.
> --
> Michael
>

I've done this on my Postgres 9.2 DB server running CentOS 6.7...

And it's pretty much what the guys told you already:


>  1. stop PostgreSQL
>  2. move the pg_xlog directory to a separate partition
>  3. create a symbolic link to point to the new partition
>  4. restart PostgreSQL

In my case, it significantly improved I/O performance.

Lucas

Re: [GENERAL] Moving pg_xlog

[Michael Paquier]

On Fri, Dec 2, 2016 at 1:04 PM, Melvin Davidson  wrote:
>  Well, while the location of pg_xlog is not currently configurable, on Linux 
> system the way to do it is  to:
>  1. stop PostgreSQL
>  2. move the pg_xlog directory to a separate partition
>  3. create a symbolic link to point to the new partition
>  4. restart PostgreSQL

Similar flow on Windows, just use a junction point for the link.
-- 
Michael


-- 
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] Moving pg_xlog

[Melvin Davidson]

On Thu, Dec 1, 2016 at 10:17 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Dec 1, 2016 at 7:59 PM, Jeff Janes  wrote:
>
>> On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder 
>> wrote:
>>
>>>
>>> I'd really like to read an explicit discussion of this in the official
>>> documentation, rather than just glean what I can from answers to
>>> questions.
>>>
>>
>> The official documentation cannot have a dissertation on every
>> combination of hardware, OS, file-system type, version of that file-system,
>> and your usage pattern.  That is inherently the realm of the wiki or the
>> blogs.
>>
>>
> ​The documentation has enough information at this level of detail that I
> wouldn't object to adding commentary addressing the above should someone
> take the time to write it.​
>
> Given that the location of pg_xlog is not "configurable" placing such
> commentary in Server Configuration would be a no-go, however.  At a quick
> glance a new section under "Server Setup and Operation - Creating a
> Database Cluster" would probably be a better home.  It already discusses
> Secondary File Systems and in many ways this is just an extension of that
> discussion.
>
> David J.
>
>
>Given that the location of pg_xlog is not "configurable"

 Well, while the location of pg_xlog is not currently configurable, on
Linux system the way to do it is  to:
 1. stop PostgreSQL
 2. move the pg_xlog directory to a separate partition
 3. create a symbolic link to point to the new partition
 4. restart PostgreSQL

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Moving pg_xlog

[David G. Johnston]

On Thu, Dec 1, 2016 at 7:59 PM, Jeff Janes  wrote:

> On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder 
> wrote:
>
>>
>> I'd really like to read an explicit discussion of this in the official
>> documentation, rather than just glean what I can from answers to
>> questions.
>>
>
> The official documentation cannot have a dissertation on every combination
> of hardware, OS, file-system type, version of that file-system, and your
> usage pattern.  That is inherently the realm of the wiki or the blogs.
>
>
​The documentation has enough information at this level of detail that I
wouldn't object to adding commentary addressing the above should someone
take the time to write it.​

Given that the location of pg_xlog is not "configurable" placing such
commentary in Server Configuration would be a no-go, however.  At a quick
glance a new section under "Server Setup and Operation - Creating a
Database Cluster" would probably be a better home.  It already discusses
Secondary File Systems and in many ways this is just an extension of that
discussion.

David J.

Re: [GENERAL] Moving pg_xlog

[Jeff Janes]

On Thu, Dec 1, 2016 at 5:55 AM, Robert Inder 
wrote:

> I'm running  Postgres9.4 in master/hot-standby mode on a few pairs of
> servers.
>
> While recovering from A Bit Of Bother last week, I came across a
> posting saying that pg_xlog should be on a separate partition.
>
> I tried to find out more about this, by consulting the PostgresQL
> documentation (i.e.
> https://www.postgresql.org/docs/9.4/static/index.html )
> But all I could find was a mention that "It is advantageous if the log
> is located on a different disk from the main database files".
>
> The questions:
> 1. WHY is this good?  Is it (just) to stop pg_xlog filling the
> database disk/partition?


More like the reverse. Running the data partition out of space is bad.
Running the pg_xlog partition out of space is worse. Running both
partitions out of space at the same time is worse yet, which of course you
will do if they are the same partition and that one partition runs out of
space.



> Or are there performance implications?
> SPECIFICALLY: my database is currently in "/", which is on SSD.  Is it
> better to move pg_xlog to another partition on the same SSD?  Or to a
> physical disk or SAN?
>


If you have something with fast fsyncs (battery backed write cache, maybe
SSD), but that is not big enough to hold your entire database, then you
would want to put your pg_xlog on that, and the rest of the database on the
rest.  (if you are doing OLTP, anyway).

On some kernels and some file systems, having a constant stream of fsyncs
(from pg_xlog) interacts poorly with having ordinary non-immediately-synced
writes (from the regular data files) on the same partition.



> 2. What are the implications for doing a base backup?  I believe I
> read that putting pg_xlog on a different partition meant it would be
> omitted from a file-system bulk copy (e.g. rsync),


rsync has lots of options to control what happens with symbolic links and
mount points.  Or to exclude certain directories, symbolic links and mount
points not withstanding.


> and this was a GOOD
> thing, because the copy operation would be faster -- not copying
> pg_xlog would not prevent the standby server from starting, because
> the information it needed would be in the WAL files that would be
> shipped separately.  Have I got that right?
>
> Finally, the suggestion.
>
> I'd really like to read an explicit discussion of this in the official
> documentation, rather than just glean what I can from answers to
> questions.
>

The official documentation cannot have a dissertation on every combination
of hardware, OS, file-system type, version of that file-system, and your
usage pattern.  That is inherently the realm of the wiki or the blogs.

Cheers,

Jeff

Re: [GENERAL] Overwrite pg_catalog?

[Michael Paquier]

On Thu, Dec 01, 2016 at 01:07:09PM +0100, Francisco Olarte wrote:
> Juliano:
> 
> On Thu, Dec 1, 2016 at 12:16 PM, Juliano  wrote:
> > I tried to restore pg_catalog to my new database, but the existing
> > pg_catalog can't be overwritten or dropped, and postgres auto creates
> > pg_catalog when I create a new DB.
> 
> This is because, in general, pg_catalog is maintained by DML
> statements, restoring it is not going to do what you think.

s/DML/DDL/
-- 
Michael


signature.asc
Description: PGP signature

Re: [GENERAL] Moving pg_xlog

[Michael Paquier]

On Thu, Dec 01, 2016 at 05:48:51PM +0200, Achilleas Mantzios wrote:
> On 01/12/2016 15:55, Robert Inder wrote:
> > I'm running  Postgres9.4 in master/hot-standby mode on a few pairs of 
> > servers.
> > 
> > While recovering from A Bit Of Bother last week, I came across a
> > posting saying that pg_xlog should be on a separate partition.
> > 
> > I tried to find out more about this, by consulting the PostgresQL
> > documentation (i.e.
> > https://www.postgresql.org/docs/9.4/static/index.html )
> > But all I could find was a mention that "It is advantageous if the log
> > is located on a different disk from the main database files".
> > 
> > The questions:
> > 1. WHY is this good?  Is it (just) to stop pg_xlog filling the
> > database disk/partition?  Or are there performance implications?
> > SPECIFICALLY: my database is currently in "/", which is on SSD.  Is it
> > better to move pg_xlog to another partition on the same SSD?  Or to a
> > physical disk or SAN?
> 
> Performance is the reason. You would benefit from moving pg_xlog to a
> different controller with its own write cache or to a different SSD with a
> write cache which is capacitor-backed. So in enterprise/server-class setups
> the above would boost the performance. Using the same SSD with a different
> partition won't give you much.

For performance, on-disk write pattern of data in pg_xlog is sequential
writes, while there will be likely random writes on the main data folder.
On top of that, moving them to a different partition gives more flexibility
in the way to tune checkpoint-related parameters using the partition space
as a constraint for retention policy and checkpoint timings.
-- 
Michael


signature.asc
Description: PGP signature

Re: [GENERAL] PostgreSQL ODBC driver for OSX 10.8

[Igor Korot]

Hi, Adrian,

On Thu, Dec 1, 2016 at 7:30 PM, Adrian Klaver  wrote:
> On 12/01/2016 04:21 PM, Igor Korot wrote:
>>
>> Hi, guys,
>> I downloaded the latest sources, but the configure failed.
>> I have OSX 10.8 here.
>>
>> Which version of the driver is compatible?
>
>
> That is going to need more information:
>
> 1) What are version of Postgres are you trying to connect to?

Version is 9.5.2

>
> 2) Do you have dev packages installed on your machine?
> The immediate problem being:
> configure: error: libpq library version >= 9.2 is required
> So what do have on on the machine in the way of Postgres now?

I don't know.
Whatever OSX 10.8 has.by default.

>
> 3) How did you get Postgres on the machine?

My plan is to try and connect to the database over the network.
It is running on the Gentoo Linux machine and was installed thru the
official Gentoo repository.

Thank you.

>
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] PostgreSQL ODBC driver for OSX 10.8

[Adrian Klaver]

On 12/01/2016 04:21 PM, Igor Korot wrote:

Hi, guys,
I downloaded the latest sources, but the configure failed.
I have OSX 10.8 here.

Which version of the driver is compatible?


That is going to need more information:

1) What are version of Postgres are you trying to connect to?

2) Do you have dev packages installed on your machine?
The immediate problem being:
configure: error: libpq library version >= 9.2 is required
So what do have on on the machine in the way of Postgres now?

3) How did you get Postgres on the machine?




--
Adrian Klaver
adrian.kla...@aklaver.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] PostgreSQL ODBC driver for OSX 10.8

[Igor Korot]

Hi, guys,
I downloaded the latest sources, but the configure failed.
I have OSX 10.8 here.

Which version of the driver is compatible?

[code]
Igors-MacBook-Air:buildMac igorkorot$ ../configure
--with-iodbc=/Library/Frameworks/iODBC.framework/iODBC
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... ../config/install-sh -c -d
checking for gawk... no
checking for mawk... no
checking for nawk... no
checking for awk... awk
checking whether make sets $(MAKE)... yes
checking whether make supports nested variables... yes
checking whether to enable maintainer-specific portions of Makefiles... no
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether we are using the GNU C compiler... yes
checking whether gcc accepts -g... yes
checking for gcc option to accept ISO C89... none needed
checking whether gcc understands -c and -o together... yes
checking for style of include used by make... GNU
checking dependency style of gcc... gcc3
checking -Wall is a valid compile option... yes
../configure: line 4390: /Library/Frameworks/iODBC.framework/iODBC:
cannot execute binary file
../configure: line 4393: /Library/Frameworks/iODBC.framework/iODBC:
cannot execute binary file
configure: using
checking last argument to SQLColAttribute is SQLLEN *... yes
checking for pg_config... /usr/bin/pg_config
checking for prove... prove
checking build system type... x86_64-apple-darwin12.5.0
checking host system type... x86_64-apple-darwin12.5.0
checking how to print strings... printf
checking for a sed that does not truncate output... /usr/bin/sed
checking for grep that handles long lines and -e... /usr/bin/grep
checking for egrep... /usr/bin/grep -E
checking for fgrep... /usr/bin/grep -F
checking for ld used by gcc...
/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld
checking if the linker
(/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld)
is GNU ld... no
checking for BSD- or MS-compatible name lister (nm)... /usr/bin/nm
checking the name lister (/usr/bin/nm) interface... BSD nm
checking whether ln -s works... yes
checking the maximum length of command line arguments... 196608
checking whether the shell understands some XSI constructs... yes
checking whether the shell understands "+="... yes
checking how to convert x86_64-apple-darwin12.5.0 file names to
x86_64-apple-darwin12.5.0 format... func_convert_file_noop
checking how to convert x86_64-apple-darwin12.5.0 file names to
toolchain format... func_convert_file_noop
checking for 
/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld
option to reload object files... -r
checking for objdump... objdump
checking how to recognize dependent libraries... pass_all
checking for dlltool... dlltool
checking how to associate runtime and link libraries... printf %s\n
checking for ar... ar
checking for archiver @FILE support... no
checking for strip... strip
checking for ranlib... ranlib
checking command to parse /usr/bin/nm output from gcc object... ok
checking for sysroot... no
checking for mt... no
checking if : is a manifest tool... no
checking for dsymutil... dsymutil
checking for nmedit... nmedit
checking for lipo... lipo
checking for otool... otool
checking for otool64... no
checking for -single_module linker flag... yes
checking for -exported_symbols_list linker flag... yes
checking for -force_load linker flag... yes
checking how to run the C preprocessor... gcc -E
checking for ANSI C header files... yes
checking for sys/types.h... yes
checking for sys/stat.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for memory.h... yes
checking for strings.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for unistd.h... yes
checking for dlfcn.h... yes
checking for objdir... .libs
checking if gcc supports -fno-rtti -fno-exceptions... yes
checking for gcc option to produce PIC... -fno-common -DPIC
checking if gcc PIC flag -fno-common -DPIC works... yes
checking if gcc static flag -static works... no
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.o... (cached) yes
checking whether the gcc linker
(/Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ld)
supports shared libraries... yes
checking dynamic linker characteristics... darwin12.5.0 dyld
checking how to hardcode library paths into programs... immediate
checking for dlopen in -ldl... yes
checking whether a program can dlopen itself... yes
checking whether a statically linked program can dlopen itself... yes
checking whether stripping libraries is possible... yes
checking if libtool supports shared 

[GENERAL] Re: [GENERAL] [GENERAL] Replication between différent versions of the same OS.

[Benoit Lobréau]

Thanks a lot. That s what I was looking for ;)

Yes, I was trying to avoid logical replication. I guess it s time for me to
delve into it...

Re: [GENERAL] [GENERAL] Replication between différent versions of the same OS.

[Jehan-Guillaume de Rorthais]

On Thu, 1 Dec 2016 20:11:06 +0100
Benoit Lobréau  wrote:

> Hi,
> 
> Is it possible to use the built in replication to replicate between two
> PostgreSQL in the same version but in different version of the same OS (Say
> Pg 9.1 Ubuntu 12 to Pg 9.1 Ubuntu 14)
> 
> I think I read in Hackers that since PostgreSQL uses the OS libraries for
> encoding. It could cause silent corruption because the encoding might be
> different between versions of the OS. But I cant find the email again so I
> can't find the exact context ... maybe I dreamed it ..

No, you did not. See:
https://www.postgresql.org/message-id/flat/BA6132ED-1F6B-4A0B-AC22-81278F5AB81E%40tripadvisor.com#ba6132ed-1f6b-4a0b-ac22-81278f5ab...@tripadvisor.com

> We would like to replicate to the other server then upgrade to a newer
> version. (the import is too long)

Use logical replication. You could use Slony for example.

Regards,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] [GENERAL] Replication between différent versions of the same OS.

[Benoit Lobréau]

Hi,

Is it possible to use the built in replication to replicate between two
PostgreSQL in the same version but in different version of the same OS (Say
Pg 9.1 Ubuntu 12 to Pg 9.1 Ubuntu 14)

I think I read in Hackers that since PostgreSQL uses the OS libraries for
encoding. It could cause silent corruption because the encoding might be
different between versions of the OS. But I cant find the email again so I
can't find the exact context ... maybe I dreamed it ..

We would like to replicate to the other server then upgrade to a newer
version. (the import is too long)


Thanks
Benoit

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

[Tom Lane]

George  writes:
> On Thu, Dec 1, 2016 at 6:58 PM, Tom Lane  wrote:
>> What
>> do you get for
>> select * from pg_stats where tablename = 'wg3ppbm_userpartner';
>> and likewise for wg3ppbm_partner?

> It is a wide table. Do you want me to dump csv here?

Shouldn't be *that* wide, with only one row in the underlying table ;-)
Maybe psql \x format would be suitable.

> In the meantime, with the help of the folks at #postgresql I was able
> to wisen up the query planner by using either one of the following two
> settings:
>  SET enable_seqscan = false
>  SET cpu_tuple_cost = 0.1

Well, that proves it is considering the indexscan option.  But there is
something funny going on, if you have stats for these tables and yet
you're getting a default rowcount estimate.

regards, tom lane


-- 
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] Index is not used for "IN (non-correlated subquery)"

[George]

On Thu, Dec 1, 2016 at 6:58 PM, Tom Lane  wrote:
> George  writes:
>> explain analyze select *
>> from wg3ppbm_transaction where partner_uuid in (
>> select p.uuid
>> from wg3ppbm_userpartner up
>> join wg3ppbm_partner p on p.id = up.partner_id
>> );
>
>> "Hash Semi Join  (cost=2.07..65628.14 rows=663727 width=380) (actual
>> time=0.346..1542.730 rows=1 loops=1)"
>> "  Hash Cond: ((wg3ppbm_transaction.partner_uuid)::text = (p.uuid)::text)"
>> "  ->  Seq Scan on wg3ppbm_transaction  (cost=0.00..54757.54
>> rows=1327454 width=380) (actual time=0.004..878.568 rows=1327587
>> loops=1)"
>
> So you're still getting the 50% default estimate, which is why it doesn't
> want to use the index ...
>
>> "  ->  Hash  (cost=2.06..2.06 rows=1 width=37) (actual
>> time=0.017..0.017 rows=1 loops=1)"
>> "Buckets: 1024  Batches: 1  Memory Usage: 5kB"
>> "->  Nested Loop  (cost=0.00..2.06 rows=1 width=37) (actual
>> time=0.011..0.012 rows=1 loops=1)"
>> "  Join Filter: (up.partner_id = p.id)"
>> "  Rows Removed by Join Filter: 1"
>> "  ->  Seq Scan on wg3ppbm_userpartner up
>> (cost=0.00..1.01 rows=1 width=4) (actual time=0.004..0.004 rows=1
>> loops=1)"
>> "  ->  Seq Scan on wg3ppbm_partner p  (cost=0.00..1.02
>> rows=2 width=41) (actual time=0.001..0.001 rows=2 loops=1)"
>
> ... and you still don't have any meaningful number of rows in
> wg3ppbm_userpartner or wg3ppbm_partner.  However, I don't understand how
> it knows that there's only one or two rows in those tables and yet is
> producing the stupid default estimate for the semijoin.  I spent some time
> trying to duplicate that behavior, without success.  What PG version is
> that, exactly?

"PostgreSQL 9.5.5 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-17), 32-bit"

> Have you vacuumed and/or analyzed those two tables?

Yes.

> What
> do you get for
>
> select * from pg_stats where tablename = 'wg3ppbm_userpartner';
> and likewise for wg3ppbm_partner?

It is a wide table. Do you want me to dump csv here?

In the meantime, with the help of the folks at #postgresql I was able
to wisen up the query planner by using either one of the following two
settings:

 SET enable_seqscan = false

 SET cpu_tuple_cost = 0.1

I think this should be helpful.


-- 
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] PostgreSQL ODBC driver for OSX 10.8

[Adrian Klaver]

On 12/01/2016 08:41 AM, Igor Korot wrote:

Adrian,

On Thu, Dec 1, 2016 at 11:37 AM, Adrian Klaver
 wrote:

On 12/01/2016 08:01 AM, Igor Korot wrote:


 Hi, ALL,
This is my first post here.
I have actually 2 questions which are kind of related.

1. Is there an OSX version of the ODBC PostgreSQL driver?
1a. If there is none - is there an instructions on how to build and
install it?



https://odbc.postgresql.org/docs/unix-compilation.html


I presume there is no OSX distribution file (.dmg), right?
Which means I'm forced to build it myself...


Should have added previously, there is a separate mailing list for psqlodbc:

https://www.postgresql.org/list/pgsql-odbc/

That would probably be a good place to ask whether having a project dmg 
version is possible.







2. Is PostgreSQL ODBC driver works with iODBC?



See above.


Thank you.





Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.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] Index is not used for "IN (non-correlated subquery)"

[Tom Lane]

George  writes:
> explain analyze select *
> from wg3ppbm_transaction where partner_uuid in (
> select p.uuid
> from wg3ppbm_userpartner up
> join wg3ppbm_partner p on p.id = up.partner_id
> );

> "Hash Semi Join  (cost=2.07..65628.14 rows=663727 width=380) (actual
> time=0.346..1542.730 rows=1 loops=1)"
> "  Hash Cond: ((wg3ppbm_transaction.partner_uuid)::text = (p.uuid)::text)"
> "  ->  Seq Scan on wg3ppbm_transaction  (cost=0.00..54757.54
> rows=1327454 width=380) (actual time=0.004..878.568 rows=1327587
> loops=1)"

So you're still getting the 50% default estimate, which is why it doesn't
want to use the index ...

> "  ->  Hash  (cost=2.06..2.06 rows=1 width=37) (actual
> time=0.017..0.017 rows=1 loops=1)"
> "Buckets: 1024  Batches: 1  Memory Usage: 5kB"
> "->  Nested Loop  (cost=0.00..2.06 rows=1 width=37) (actual
> time=0.011..0.012 rows=1 loops=1)"
> "  Join Filter: (up.partner_id = p.id)"
> "  Rows Removed by Join Filter: 1"
> "  ->  Seq Scan on wg3ppbm_userpartner up
> (cost=0.00..1.01 rows=1 width=4) (actual time=0.004..0.004 rows=1
> loops=1)"
> "  ->  Seq Scan on wg3ppbm_partner p  (cost=0.00..1.02
> rows=2 width=41) (actual time=0.001..0.001 rows=2 loops=1)"

... and you still don't have any meaningful number of rows in
wg3ppbm_userpartner or wg3ppbm_partner.  However, I don't understand how
it knows that there's only one or two rows in those tables and yet is
producing the stupid default estimate for the semijoin.  I spent some time
trying to duplicate that behavior, without success.  What PG version is
that, exactly?  Have you vacuumed and/or analyzed those two tables?  What
do you get for

select * from pg_stats where tablename = 'wg3ppbm_userpartner';

and likewise for wg3ppbm_partner?

regards, tom lane


-- 
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] Overwrite pg_catalog?

[Joshua D. Drake]

On 12/01/2016 03:16 AM, Juliano wrote:

Hi everyone,

I tried to restore pg_catalog to my new database, but the existing
pg_catalog can't be overwritten or dropped, and postgres auto creates
pg_catalog when I create a new DB.

So, there is a way to restore the pg_catalog to a new database?


Why would you do that? You should be restoring from a proper backup 
which will already have everything it needs to populate the new 
pg_catalog correctly.


JD



Regards,
Juliano



--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


--
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] PostgreSQL ODBC driver for OSX 10.8

[Adrian Klaver]

On 12/01/2016 08:41 AM, Igor Korot wrote:

Adrian,

On Thu, Dec 1, 2016 at 11:37 AM, Adrian Klaver
 wrote:

On 12/01/2016 08:01 AM, Igor Korot wrote:


 Hi, ALL,
This is my first post here.
I have actually 2 questions which are kind of related.

1. Is there an OSX version of the ODBC PostgreSQL driver?
1a. If there is none - is there an instructions on how to build and
install it?



https://odbc.postgresql.org/docs/unix-compilation.html


I presume there is no OSX distribution file (.dmg), right?


All the project files I know of are found at the link below and they do 
not include a *.dmg.


https://www.postgresql.org/ftp/odbc/versions/

A dmg is going to need to come from someone else. That triggered a 
thought. You might want to take a look at:


https://www.bigsql.org/postgresql/installers.jsp


Which means I'm forced to build it myself...




2. Is PostgreSQL ODBC driver works with iODBC?



See above.


Thank you.





Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.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] PostgreSQL ODBC driver for OSX 10.8

[Igor Korot]

Adrian,

On Thu, Dec 1, 2016 at 11:37 AM, Adrian Klaver
 wrote:
> On 12/01/2016 08:01 AM, Igor Korot wrote:
>>
>>  Hi, ALL,
>> This is my first post here.
>> I have actually 2 questions which are kind of related.
>>
>> 1. Is there an OSX version of the ODBC PostgreSQL driver?
>> 1a. If there is none - is there an instructions on how to build and
>> install it?
>
>
> https://odbc.postgresql.org/docs/unix-compilation.html

I presume there is no OSX distribution file (.dmg), right?
Which means I'm forced to build it myself...

>
>> 2. Is PostgreSQL ODBC driver works with iODBC?
>
>
> See above.

Thank you.

>
>>
>> Thank you.
>>
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] PostgreSQL ODBC driver for OSX 10.8

[Adrian Klaver]

On 12/01/2016 08:01 AM, Igor Korot wrote:

 Hi, ALL,
This is my first post here.
I have actually 2 questions which are kind of related.

1. Is there an OSX version of the ODBC PostgreSQL driver?
1a. If there is none - is there an instructions on how to build and install it?


https://odbc.postgresql.org/docs/unix-compilation.html


2. Is PostgreSQL ODBC driver works with iODBC?


See above.



Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL ODBC driver for OSX 10.8

[Igor Korot]

 Hi, ALL,
This is my first post here.
I have actually 2 questions which are kind of related.

1. Is there an OSX version of the ODBC PostgreSQL driver?
1a. If there is none - is there an instructions on how to build and install it?
2. Is PostgreSQL ODBC driver works with iODBC?

Thank you.


-- 
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] Moving pg_xlog

[Achilleas Mantzios]

On 01/12/2016 15:55, Robert Inder wrote:

I'm running  Postgres9.4 in master/hot-standby mode on a few pairs of servers.

While recovering from A Bit Of Bother last week, I came across a
posting saying that pg_xlog should be on a separate partition.

I tried to find out more about this, by consulting the PostgresQL
documentation (i.e.
https://www.postgresql.org/docs/9.4/static/index.html )
But all I could find was a mention that "It is advantageous if the log
is located on a different disk from the main database files".

The questions:
1. WHY is this good?  Is it (just) to stop pg_xlog filling the
database disk/partition?  Or are there performance implications?
SPECIFICALLY: my database is currently in "/", which is on SSD.  Is it
better to move pg_xlog to another partition on the same SSD?  Or to a
physical disk or SAN?


Performance is the reason. You would benefit from moving pg_xlog to a different controller with its own write cache or to a different SSD with a write cache which is capacitor-backed. So in 
enterprise/server-class setups the above would boost the performance. Using the same SSD with a different partition won't give you much.




2. What are the implications for doing a base backup?  I believe I
read that putting pg_xlog on a different partition meant it would be
omitted from a file-system bulk copy (e.g. rsync), and this was a GOOD
thing, because the copy operation would be faster -- not copying
pg_xlog would not prevent the standby server from starting, because
the information it needed would be in the WAL files that would be
shipped separately.  Have I got that right?
Rsync does cross fs boundaries unless you give it the -x option. It is true that the files in pg_xlog won't be useful to be taken in the backup. However the wal files to be shipped separately is not 
smth done by itself, you need to enable/implement WAL archiving. What you describe seems to be the "legacy" old-fashioned way circa 9.0. pg_basebackup (9.1) is more convenient, can create complete 
standalone copies (without the need of any additional wals), can use wal streaming so that you don't depend on wal archiving or wal_keep_segment, supports repl slots, can create a ready to go hot 
standby, etc.



Finally, the suggestion.

I'd really like to read an explicit discussion of this in the official
documentation, rather than just glean what I can from answers to
questions.
The possibility of moving pg_xlog to another disk is mentioned in the
documentation, but I almost missed it because it is in "the wrong
place".  It is in Section 29.5 -- "Reliability and the Write Ahead
Log" / "WAL Internals".  But I wasn't interested in anything INTERNAL:
I wanted to know where I should try to locate it/them.  So I'd looked
in "the obvious places" -- Section 18 (Server configuration), and in
particular 18.2 "File Locations".  Could I suggest that the motivation
for doing this, and the consequences for backups, should be discussed
in "the right place" -- in or near the section that talks about file
locations in the context of server configuration.


All I can tell you is I haven't found one single piece of free (or not so free) 
software with more complete documentation than pgsql.


Robert.




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt



--
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] Overwrite pg_catalog?

[David G. Johnston]

On Thu, Dec 1, 2016 at 4:16 AM, Juliano  wrote:

> Hi everyone,
>
> I tried to restore pg_catalog to my new database, but the existing
> pg_catalog can't be overwritten or dropped, and postgres auto creates
> pg_catalog when I create a new DB.
>
> So, there is a way to restore the pg_catalog to a new database?
>
>
​I suggest you explain the actual problem ​you are trying to solve - the
one for which you believe restoring pg_catalog is a solution.

David J.

Re: [GENERAL] issue with host name lookup in PQconnectdb

[Tom Lane]

dharani kumar  writes:
> The problem i am facing is that PQconnectdb is blocking even though i
> specified connect_timeout=10. My observation is below.
> 1. To simulate this issue, i stopped the postgreSQL-x64-9.4 service and
> executed my application.
> 2. host=localhost is used, database present in the same machine.
> 3. I have a service created by NSSM, created out of my application exe.
> 4. My application doesn't blocks, the PQconnectDB fails instantaneously but
> when ran as a service PQconnectDB blocks. No difference in code and
> environment between exe and service.
> 5. The blocking issue in service gets eliminated when i replace host with
> hostaddr=127.0.0.1. I thought maybe DNS lookup was the issue.

AFAIK this is expected behavior: libpq is not able to make its DNS lookups
nonblocking.  The hostaddr option was added specifically so there is a way
to make connections without relying on DNS.

regards, tom lane


-- 
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] Overwrite pg_catalog?

[Melvin Davidson]

On Thu, Dec 1, 2016 at 7:07 AM, Francisco Olarte 
wrote:

> Juliano:
>
> On Thu, Dec 1, 2016 at 12:16 PM, Juliano  wrote:
> > I tried to restore pg_catalog to my new database, but the existing
> > pg_catalog can't be overwritten or dropped, and postgres auto creates
> > pg_catalog when I create a new DB.
>
> This is because, in general, pg_catalog is maintained by DML
> statements, restoring it is not going to do what you think.
>
> I.e., if you create a table, a row goes into pg_class, but if you
> somehow manage to insert into pg_class a table is not properly created
> ( more things need to be done ).
>
> Copying pg_catalog from one db to other is like trying to copy the
> root dir and FAT from one floppy ( how old fashioned ) to other, it
> cannot be done with normal tools and probably won't do what you think
> it does.
>
>
> > So, there is a way to restore the pg_catalog to a new database?
>
> Probably not, but this has the faint smell of http://xyproblem.info/ ,
> what are you truing to achieve by doing that?
>
> Francisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



*Juliano,*

*The right way to do what you want is to:*

*A:*

*1. pg_dump from the database you want to copy
https://www.postgresql.org/docs/9.6/static/app-pgdump.html
*

*2. pg_restore the dump file too the new database.
https://www.postgresql.org/docs/9.6/static/app-pgrestore.html
*

*OR*

*B:*

*1: pg_dumpall from the cluster you want to copy
https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html
*

*2: use psql to restore the dump file into the new cluster
https://www.postgresql.org/docs/9.6/static/app-psql.html
*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] Moving pg_xlog

[Robert Inder]

I'm running  Postgres9.4 in master/hot-standby mode on a few pairs of servers.

While recovering from A Bit Of Bother last week, I came across a
posting saying that pg_xlog should be on a separate partition.

I tried to find out more about this, by consulting the PostgresQL
documentation (i.e.
https://www.postgresql.org/docs/9.4/static/index.html )
But all I could find was a mention that "It is advantageous if the log
is located on a different disk from the main database files".

The questions:
1. WHY is this good?  Is it (just) to stop pg_xlog filling the
database disk/partition?  Or are there performance implications?
SPECIFICALLY: my database is currently in "/", which is on SSD.  Is it
better to move pg_xlog to another partition on the same SSD?  Or to a
physical disk or SAN?

2. What are the implications for doing a base backup?  I believe I
read that putting pg_xlog on a different partition meant it would be
omitted from a file-system bulk copy (e.g. rsync), and this was a GOOD
thing, because the copy operation would be faster -- not copying
pg_xlog would not prevent the standby server from starting, because
the information it needed would be in the WAL files that would be
shipped separately.  Have I got that right?

Finally, the suggestion.

I'd really like to read an explicit discussion of this in the official
documentation, rather than just glean what I can from answers to
questions.
The possibility of moving pg_xlog to another disk is mentioned in the
documentation, but I almost missed it because it is in "the wrong
place".  It is in Section 29.5 -- "Reliability and the Write Ahead
Log" / "WAL Internals".  But I wasn't interested in anything INTERNAL:
I wanted to know where I should try to locate it/them.  So I'd looked
in "the obvious places" -- Section 18 (Server configuration), and in
particular 18.2 "File Locations".  Could I suggest that the motivation
for doing this, and the consequences for backups, should be discussed
in "the right place" -- in or near the section that talks about file
locations in the context of server configuration.

Robert.

-- 
Robert Inder,0131 229 1052 / 07808 492 213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than words


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] issue with host name lookup in PQconnectdb

[dharani kumar]

The problem i am facing is that PQconnectdb is blocking even though i
specified connect_timeout=10. My observation is below.
1. To simulate this issue, i stopped the postgreSQL-x64-9.4 service and
executed my application.
2. host=localhost is used, database present in the same machine.
3. I have a service created by NSSM, created out of my application exe.
4. My application doesn't blocks, the PQconnectDB fails instantaneously but
when ran as a service PQconnectDB blocks. No difference in code and
environment between exe and service.
5. The blocking issue in service gets eliminated when i replace host with
hostaddr=127.0.0.1. I thought maybe DNS lookup was the issue.

With these observation i tried to understand the nature and root cause of
the issue, could someone throw a light on this?

-Dharani

Re: [GENERAL] About the MONEY type

[rob stone]

My two cents . . .
On Wed, 2016-11-30 at 13:35 -0800, John R Pierce wrote:
> On 11/30/2016 12:16 PM, John McKown wrote:
> > Speaking generically, I guess maybe MONEY needs to be somewhat
> > like a TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can contain
> > a TIMEZONE. I guess a MONEY type should contain a modifier
> > identifying the issuer of the currency (E.g. U.S. Dollar vs
> > Canadian Dollar vs. Yen vs. Yuan vs. "precious metal").
> > 
> > 
>  
> and then it would need to be able to convert between all those
> units?    great fun.   it probably needs a time too, as those
> conversion units vary with time.   worse, they vary with where you
> convert the money and which way, and how much the converter
> skims  In the real world,  US$ -> € -> US$  will not give you
> back the same amount.
> 
> 


I don't believe the OP is talking about currency conversions using
exchange rates.

It sounds like he would like a printf style string held in the same
column so that a select of that column would return a string formatted
by the printf style arguments, and presumably any arithmetic operations
would return the correct result. Complicated.

Currently, working in multi-currency environments you need to have
three columns -- one defined as NUMERIC(15,3) another to hold the ISO
currency code and the date. The date is necessary due to countries
shifting the decimal place leftwards due to inflation. E.g., inflation
in Venuzuela is around 1,500%pa at the moment. Ergo, the paper money
becomes worthless and if you are still using computers with 32 bit
integers you end up doing addition by hand.



-- 
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] btree gist indices, null and open-ended tsranges

[Francisco Olarte]

Hi Chris:

On Thu, Dec 1, 2016 at 12:56 PM, Chris Withers  wrote:
> So, first observation: if I make room nullable, the exclude constraint does
> not apply for rows that have a room of null. I guess that's to be expected,
> right?

I would expect it, given:

n=> select null=null, null<>null, not (null=null);
 ?column? | ?column? | ?column?
--+--+--
  |  |
(1 row)

Those are nulls, BTW:

n=> select (null=null) is null, (null<>null) is null, (not (null=null)) is null;
 ?column? | ?column? | ?column?
--+--+--
 t| t| t
(1 row)

I.e., the same happens with a nullable unique column, you can have one
of each not null values and as many nulls as you want.

SQL null is a strange beast.


Francisco Olarte.


-- 
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] Overwrite pg_catalog?

[Francisco Olarte]

Juliano:

On Thu, Dec 1, 2016 at 12:16 PM, Juliano  wrote:
> I tried to restore pg_catalog to my new database, but the existing
> pg_catalog can't be overwritten or dropped, and postgres auto creates
> pg_catalog when I create a new DB.

This is because, in general, pg_catalog is maintained by DML
statements, restoring it is not going to do what you think.

I.e., if you create a table, a row goes into pg_class, but if you
somehow manage to insert into pg_class a table is not properly created
( more things need to be done ).

Copying pg_catalog from one db to other is like trying to copy the
root dir and FAT from one floppy ( how old fashioned ) to other, it
cannot be done with normal tools and probably won't do what you think
it does.


> So, there is a way to restore the pg_catalog to a new database?

Probably not, but this has the faint smell of http://xyproblem.info/ ,
what are you truing to achieve by doing that?

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] btree gist indices, null and open-ended tsranges

[Chris Withers]

Hi All,

Working with the exclude constraint example from 
https://www.postgresql.org/docs/current/static/rangetypes.html:


CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
room text,
during tsrange,
EXCLUDE USING GIST (room WITH =, during WITH &&)
);

So, first observation: if I make room nullable, the exclude constraint 
does not apply for rows that have a room of null. I guess that's to be 
expected, right?


Next question: if lots of rows have open-ended periods
(eg: [, 2010-01-01 15:00) or [2010-01-01 14:00,)), how does that affect 
the performance of the btree gist index backing the exclude constraint?


cheers,

Chris


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Overwrite pg_catalog?

[Juliano]

Hi everyone,

I tried to restore pg_catalog to my new database, but the existing pg_catalog 
can't be overwritten or dropped, and postgres auto creates pg_catalog when I 
create a new DB.

So, there is a way to restore the pg_catalog to a new database?

Regards,
Juliano

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

[George]

On Wed, Nov 30, 2016 at 10:08 PM, George  wrote:
> On Wed, Nov 30, 2016 at 8:44 PM, Tom Lane  wrote:
>> Merlin Moncure  writes:
>>> On Wed, Nov 30, 2016 at 11:05 AM, George  wrote:
 So there is definitely something wrong here. This situation makes many
 row-level security use cases cumbersome since you need to have
 almost the same WHERE clause both in the row-level security policy and
 in every SELECT query in order for the index to be used.
>>
>>> can you give EXPLAIN ANALYZE for the 'good' query and the 'bad' query?
>>
>> Planning for queries affected by RLS is definitely an area where we need
>> to improve (I'm working on a patch for that).  Whether the OP's particular
>> query is being hit by that is impossible to tell, though, since there
>> isn't any actual RLS usage in the doubtless-oversimplified example.
>
> The example is not over-simplified, I basically just took the clause
> that the RLS would have to add and stuck it in the WHERE. Thus I
> verified that even the normal, non-RLS planner is affected.
>
> When I get to work tomorrow morning (Europe) I will post the EXPLAIN
> ANALYZE output.

Here are the EXPLAIN ANALYZE results:

explain analyze
select *
from wg3ppbm_transaction
where partner_uuid in ('80228212-2247-4bdd-a130-80239cb33c5c');

"Index Scan using wg3ppbm_transaction_f9b3d985 on wg3ppbm_transaction
(cost=0.43..2838.57 rows=8186 width=380) (actual time=0.458..5.265
rows=7827 loops=1)"
"  Index Cond: ((partner_uuid)::text =
'80228212-2247-4bdd-a130-80239cb33c5c'::text)"
"Planning time: 0.155 ms"
"Execution time: 6.992 ms"


explain analyze select *
from wg3ppbm_transaction where partner_uuid in (
select p.uuid
from wg3ppbm_userpartner up
join wg3ppbm_partner p on p.id = up.partner_id
);

"Hash Semi Join  (cost=2.07..65628.14 rows=663727 width=380) (actual
time=0.346..1542.730 rows=1 loops=1)"
"  Hash Cond: ((wg3ppbm_transaction.partner_uuid)::text = (p.uuid)::text)"
"  ->  Seq Scan on wg3ppbm_transaction  (cost=0.00..54757.54
rows=1327454 width=380) (actual time=0.004..878.568 rows=1327587
loops=1)"
"  ->  Hash  (cost=2.06..2.06 rows=1 width=37) (actual
time=0.017..0.017 rows=1 loops=1)"
"Buckets: 1024  Batches: 1  Memory Usage: 5kB"
"->  Nested Loop  (cost=0.00..2.06 rows=1 width=37) (actual
time=0.011..0.012 rows=1 loops=1)"
"  Join Filter: (up.partner_id = p.id)"
"  Rows Removed by Join Filter: 1"
"  ->  Seq Scan on wg3ppbm_userpartner up
(cost=0.00..1.01 rows=1 width=4) (actual time=0.004..0.004 rows=1
loops=1)"
"  ->  Seq Scan on wg3ppbm_partner p  (cost=0.00..1.02
rows=2 width=41) (actual time=0.001..0.001 rows=2 loops=1)"
"Planning time: 1.484 ms"
"Execution time: 1542.799 ms"


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general