Hello.
How to use tid scans? This below not works :-(
Always is used merge join.
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 as select i,hashint4(i)::text from
generate_series(1,1) as a(i);
DROP TABLE IF EXISTS test2;
CREATE TABLE test2 as select j,j%1 as i,null::tid as ct from
gener
Mario Splivalo writes:
> I have simple database schema, containing just three tables:
>
> samples, drones, drones_history.
>
> Now, those tables hold data for the drones for a simulation. Each simulation
> dataset will grow to around 10 GB in around 6 months.
>
> Since the data is not related in a
On 11/30/2010 12:45 PM, Dimitri Fontaine wrote:
Mario Splivalo writes:
I have simple database schema, containing just three tables:
samples, drones, drones_history.
Now, those tables hold data for the drones for a simulation. Each simulation
dataset will grow to around 10 GB in around 6 month
Hi,
I am working on a performance issue with a partitioned table. Some of my sql
statements against this partition table is in waiting state for long time. I
have queried waiting=true in pg_stat_activity. Now, is there a way to find
out which sql is making other statements to wait.
Thanks for you
I'm just back from vacation, so I apologize in advance if I missed
anything of importance. Here is something to consider:
Instead of using the statement you used to create the table, try the
following:
CREATE TABLE drones_history (
drone_id integer not null,
sample_id integer
pasman pasma*ski wrote:
> How to use tid scans?
Write a query where they are the fastest way to retrieve the data,
and make sure your PostgreSQL installation is properly configured.
> This below not works :-( Always is used merge join.
> SELECT * FROM test1 join test2 on(test1.ctid=test2.ct
"Kevin Grittner" writes:
> pasman pasma*ski wrote:
>> This below not works :-( Always is used merge join.
>> SELECT * FROM test1 join test2 on(test1.ctid=test2.ct)
> You're reading through the entirety of two tables matching rows
> between them. What makes you think random access would be fas
bakkiya wrote:
> I am working on a performance issue with a partitioned table. Some
> of my sql statements against this partition table is in waiting
> state for long time. I have queried waiting=true in
> pg_stat_activity. Now, is there a way to find out which sql is
> making other statements t
On Tue, Nov 30, 2010 at 4:38 AM, bakkiya wrote:
>
> Hi,
> I am working on a performance issue with a partitioned table. Some of my sql
> statements against this partition table is in waiting state for long time. I
> have queried waiting=true in pg_stat_activity. Now, is there a way to find
> out w
I saw a presentation from Heroku where they discussed using a similar
paradigm, and they ran into trouble once they hit a couple thousand
databases. If memory serves, this was on an older version of
PostgreSQL and may not be relevant with 9.0 (or even 8.4?), but you
may want to try to track down on
I have a query that's running an IN/Subselect that joins three different
tables and gets a list of IDs to compare against... the subselect
basically looks for records through a join table based on the 3rd
table's name, similar to:
... IN (SELECT id FROM foo, foo_bar, bar
WHERE foo.id =
Having that many instances is not practical at all, so I'll have as many
databases as I have 'realms'. I'll use pg_dump | nc and nc | psql to
move databases
Mario
Then you can use schemas, too, it'll be easier.
--
Sent via pgsql-performance mailing list (pgsql-performance@po
"T.H." wrote:
> Also, are there any better ways you can think of doing such an IN
> query, using non-subselect means that might be more efficient?
Have you tried the EXISTS predicate?
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to yo
On 11/29/2010 05:47 PM, Pierre C wrote:
realm_51=# vacuum analyze verbose drones;
INFO: vacuuming "public.drones"
INFO: scanned index "drones_pk" to remove 242235 row versions
DETAIL: CPU 0.02s/0.11u sec elapsed 0.28 sec.
INFO: "drones": removed 242235 row versions in 1952 pages
DETAIL: CPU 0.01s
On 11/29/2010 05:53 PM, Pierre C wrote:
Yes, since (sample_id, drone_id) is primary key, postgres created
composite index on those columns. Are you suggesting I add two more
indexes, one for drone_id and one for sample_id?
(sample_id,drone_id) covers sample_id but if you make searches on
dron
On 11/30/10 5:54 PM, Kevin Grittner wrote:
"T.H." wrote:
Also, are there any better ways you can think of doing such an IN
query, using non-subselect means that might be more efficient?
Have you tried the EXISTS predicate?
-Kevin
Just looking into it now, thanks for the suggestion. Is th
On 30/11/10 05:53, Pierre C wrote:
Yes, since (sample_id, drone_id) is primary key, postgres created
composite index on those columns. Are you suggesting I add two more
indexes, one for drone_id and one for sample_id?
(sample_id,drone_id) covers sample_id but if you make searches on
drone_i
On 11/30/2010 05:26 PM, Mladen Gogala wrote:
At the beginning of the load, you should defer all of the deferrable
constraints, setting constraints deferred and issuing the copy statement
within a transaction block, like this:
scott=# begin; BEGIN
Time: 0.203 ms
scott=# set constraints all deferr
Now I tried removing the constraints from the history table (including
the PK) and the inserts were fast. After few 'rounds' of inserts I added
constraints back, and several round after that were fast again. But then
all the same. Insert of some 11k rows took 4 seconds (with all
constrain
On Tue, Nov 30, 2010 at 3:23 PM, T.H. wrote:
> Just looking into it now, thanks for the suggestion. Is there a reason that
> EXISTS is generally faster than IN for this sort of query?
>
> -Tristan
Exists will return immediately upon finding a match -- assuming there is one.
--
Sent via pgsql-pe
On Sun, 2010-11-28 at 12:46 +0100, Mario Splivalo wrote:
> The database for monitoring certain drone statuses is quite simple:
>
> This is the slow part:
> INSERT INTO drones_history (sample_id, drone_id, drone_log_notice,
> drone_temperature, drone_pressure)
> SELECT * FROM tmpUpdate;
>
> For
Greg Smith wrote:
> Kevin Grittner wrote:
> > I assume that we send a full
> > 8K to the OS cache, and the file system writes disk sectors
> > according to its own algorithm. With either platters or BBU cache,
> > the data is persisted on fsync; why do you see a risk with one but
> > not the other
Kevin Grittner wrote:
> Greg Smith wrote:
>
> > I think Kevin's point here may be that if your fsync isn't
> > reliable, you're always in trouble. But if your fsync is good,
> > even torn pages should be repairable by the deltas written to the
> > WAL
>
> I was actually just arguing that a BB
Greg Smith wrote:
> Tom Lane wrote:
> > You've got entirely too simplistic a view of what the "delta" might be,
> > I fear. In particular there are various sorts of changes that involve
> > inserting the data carried in the WAL record and shifting pre-existing
> > data around to make room, or remo
24 matches
Mail list logo
