Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:43 PM, Tom Lane wrote: > jonathan vanasco writes: > > Can anyone explain to me why the following is valid (running 9.6) ? > > > SELECT foo_id > > FROM example_a__data > > WHERE foo_id IN (SELECT bar_id FROM

Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > > SELECT foo_id > FROM example_a__data > WHERE foo_id IN (SELECT bar_id FROM example_a__rollup) > ; > > > ​Or write it the idiomatic way (i.e., as a proper semi-join): ​SELECT foo_id FROM example_a__data WHERE EXISTS

Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:56 PM, jonathan vanasco wrote: > thanks all! > > On Apr 20, 2017, at 6:42 PM, David G. Johnston wrote: > > ​Subqueries can see all columns of the parent. When the subquery actually > uses one of them it is called a "correlated subquery". > > > i

Re: [GENERAL] why isn't this subquery wrong?

thanks all! On Apr 20, 2017, at 6:42 PM, David G. Johnston wrote: > ​Subqueries can see all columns of the parent. When the subquery actually > uses one of them it is called a "correlated subquery". i thought a correlated subquery had to note that table/alias, not a raw column. I guess

Re: [GENERAL] why isn't this subquery wrong?

jonathan vanasco writes: > Can anyone explain to me why the following is valid (running 9.6) ? > SELECT foo_id > FROM example_a__data > WHERE foo_id IN (SELECT bar_id FROM example_a__rollup) > ; Per the SQL standard, bar_id is interpreted as an "outer

Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > postgres doesn't raise an error because example_a__data does have a bar_id > -- but example_a__rollup doesn't and there's no explicit correlation in the > query. > > ​Subqueries can see all columns of the parent. When

Re: [GENERAL] why isn't this subquery wrong?

On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > > I ran into an issue while changing a database schema around. Some queries > still worked, even though I didn't expect them to. > > Can anyone explain to me why the following is valid (running 9.6) ? > > schema > >

[GENERAL] why isn't this subquery wrong?

I ran into an issue while changing a database schema around. Some queries still worked, even though I didn't expect them to. Can anyone explain to me why the following is valid (running 9.6) ? schema CREATE TEMPORARY TABLE example_a__data ( foo_id INT, bar_id INT );

Re: [GENERAL] Large data and slow queries

Scott, Vick, the vast majority of the data is generic. But there are some specific events we need to look up quickly which are probably less than a few 100,000 records. We did evaluate partial indexes vs full indexes. The partial index speeds up our specific queries significantly while only taking

Re: [GENERAL] Large data and slow queries

Andrew, how would timescaledb compare to citus - and is timescaledb an extension to postgres or is it an entirely separate system? On 21 April 2017 at 02:44, Andrew Staller wrote: > Awesome thread. > > Samuel, > > Just wanted you to be aware of the work we're doing at

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, Apr 19, 2017 at 9:55 PM, George Neuner wrote: > On Wed, 19 Apr 2017 16:28:13 -0700, Rj Ewing > wrote: > > >okay, messing around a bit more with the secondary k,v table it seems like > >this could be a good solution.. > > > >I created a keys

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, Apr 19, 2017 at 8:09 PM, Jeff Janes wrote: > > Your best bet might be to ignore the per-field searching in the initial > (indexed) pass of the query to get everything that has all the search > terms, regardless of which field they occur in. And the re-check whether

Re: [GENERAL] Large data and slow queries

On Thu, Apr 20, 2017 at 6:30 AM, Vick Khera wrote: > I'm curious why you have so many partial indexes. Are you trying to make > custom indexes per query? It seems to me you might want to consider making > the indexes general, and remove the redundant ones (that have the same >

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, Apr 19, 2017 at 6:44 PM, George Neuner wrote: > > On Wed, 19 Apr 2017 11:57:26 -0700, Rj Ewing > wrote: > > >I did some testing using a secondary table with the key, value column. > >However I don't think this will provide the performance that we

Re: [GENERAL] Unable to upload backups

On 04/19/2017 11:24 PM, Ron Ben wrote: OK. I think I found a bug in PostgreSQL (9.3). When I do: CREATE ROLE ronb SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION; Everything works. I can create the schemas and upload the backup correclty. But if I do: CREATE ROLE "ronb" LOGIN

Re: [GENERAL] Large data and slow queries

Awesome thread. Samuel, Just wanted you to be aware of the work we're doing at TimescaleDB ( http://www.timescale.com/), a time-series database extension for PostgreSQL. Some of how we might help you: - automatic partitioning by space (primary key - like country_id, for instance) and time. This

Re: [GENERAL] UDP buffer drops / statistics collector

On Thursday, April 20, 2017 3:38:42 AM EDT Tim Kane wrote: > The pgss_query_texts.stat still wants to live in the default *pg_stat_tmp* > directory, wether by design or not.. but that's a non-issue for me now. A 30 second investigation of the source seems to indicate that that directory is

Re: [GENERAL] cluster on brin indexes?

Samuel Williams wrote: > I see this, but no follow up: > > https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com > > So, is it possible or not? The general idea seems like it should be doable, but I haven't looked at it in detail.

Re: [GENERAL] Large data and slow queries

I'm curious why you have so many partial indexes. Are you trying to make custom indexes per query? It seems to me you might want to consider making the indexes general, and remove the redundant ones (that have the same prefix list of indexed fields). Secondly your table is 102Gb. Clearly there's

Re: [GENERAL] Why is this functional index not used?

On Mon, Mar 20, 2017 at 10:58 PM, ibeq GmbH wrote: > Given a country table and an order table: > > > > CREATE TABLE g.country > > ( > > -- inherited from table g.standard: oid uuid NOT NULL, > > -- … some more columns inherited… > > lisocode integer NOT NULL, -- Numeric ISO

Re: [GENERAL] Why so long?

On Wed, Apr 19, 2017 at 8:54 PM, Steve Clark wrote: > Hello, > > I am confused. I have a table that has an incrementing primary key id. > > When I select max(id) from table is returns almost instantly but > when I select min(id) from table it takes longer than I want

Re: [GENERAL] UDP buffer drops / statistics collector

Ok, fixed it! :D Posting here for future me (others like me). It would seem (having not read kernel source) that increasing the kernel buffer sizes (rmin_default / rmin_max) does *not* take effect for any processes that are *already* bound or listening to a port/socket. I had previously assumed

[GENERAL] Unable to upload backups

Please don't top-post, and trim some of the cruft while you are at it... On Wednesday, April 19, 2017, Ron Ben > wrote: > > OK. I think I found a bug in PostgreSQL (9.3). > No, it's just that roles and grants are

Re: [GENERAL] Unable to upload backups

OK. I think I found a bug in PostgreSQL (9.3).   When I do:   CREATE ROLE ronb  SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;   Everything works. I can create the schemas and upload the backup correclty.   But if I do: CREATE ROLE "ronb" LOGIN  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE