In response to mike :
> Hi All,
>
> I have a poor performance SQL as following. The table has about 200M
> records, each employee have average 100 records. The query lasts about
> 3 hours. All I want is to update the flag for highest version of each
> client's record. Any suggestion is welcome!
>
In response to AI Rumman :
> I have a table.
>
> \d email_track
> Table "public.email_track"
> Column | Type | Modifiers
> +-+
> crmid | integer | not null default 0
> mailid | integer | not null default 0
> count | integer |
> Indexes:
> "
In response to Elias Ghanem :
> Hi,
> I have a question concerning the uses of indexes in Postgresql.
> I red that in PG a query can not use more than one index per table: "a query
> or
> data manipulation command can use at most one index per table".
That's not true, but it's true for MySQL, afa
In response to Rob Wultsch :
> On Mon, Jul 12, 2010 at 4:15 AM, A. Kretschmer
> wrote:
> > Use timeofday() instead, now() returns the transaction starting time.
>
>
> Is this part of the SQL standard?
Don't know, sorry.
Andreas
--
Andreas Kretschmer
Kontakt: H
In response to atul.g...@globaldatapoint.com :
> Hi,
>
>
>
> I need to log the start and end time of the procedures in a table. But the
> start and end time are same. This is how I recreated the issue.
>
>
>
> create table test_time (time timestamp);
>
> delete from test_time;
>
> insert
In response to Greg Smith :
> For details about what the database does there, see "Inside the
> PostgreSQL Buffer Cache" at http://projects.2ndquadrant.com/talks
Nice paper, btw., thanks for that!
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr:
In response to Mozzi :
> Hi
>
> Thanx mate Create Index seems to be the culprit.
> Is it normal to just use 1 cpu tho?
If you have only one client, yes. If you have more then one active
connections, every connection will use one CPU. In your case: create
index can use only one CPU.
Regards, And
In response to Tyler Hildebrandt :
> We're using a function that when run as a select statement outside of the
> function takes roughly 1.5s to complete whereas running an identical
> query within a function is taking around 55s to complete.
>
> select * from fn_medirota_get_staff_leave_summary(6
In response to Jon Nelson :
> On Mon, May 17, 2010 at 5:10 AM, Pierre C wrote:
> > - or use a JOIN delete with a virtual VALUES table
> > - or fill a temp table with ids and use a JOIN DELETE
>
> What is a virtual VALUES table? Can you give me an example of using a
> virtual table with selects, j
In response to Kaloyan Iliev Iliev :
> Hi,
> Can anyone suggest why this query so slow.
>-> Index Scan using
> ms_commands_history_ms_device_id_idx on ms_commands_history ch
> (cost=0.00..4.33 rows=1 width=8) (actual time=0.163..25254.004 rows=9807
> loops=1)
Estimat
In response to Angayarkanni :
> Hi,
>
> I am the beginner of pgpsql
>
>
> I need to select from two tables say T1,T2 by UNION
>
> when the value is from T1 the output should by a 1 and
> when the value from T2 the out put should be 2
>
> when T1 Hits values should be 1 and when T2 hits the va
In response to Kevin Kempter :
> Hi All;
>
> I have a table that has daily partitions.
>
> The check constraints look like this:
> CHECK (timezone('EST'::text, insert_dt) >= '2010-01-01'::date
> AND timezone('EST'::text, insert_dt) < '2010-01-02'::date)
>
> each partition has this index:
>
In response to Kenneth Marshall :
> > > How many rows do you have in your table? If there are relatively few,
> > > it probably guesses it to be cheaper to do a sequential scan and
> > > calculate lower values on-the-fly rather than bother with the index.
> >
> > That's one reason, an other reaso
In response to Thom Brown :
> On 18 February 2010 11:55, AI Rumman wrote:
> > "Not like" operation does not use index.
> >
> > select * from vtiger_contactscf where lower(cf_1253) not like
> > lower('Former%')
> >
> > I created index on lower(cf_1253).
> >
> > How can I ensure index usage in not l
In response to Matthew Wakeling :
> On Mon, 25 Jan 2010, A. Kretschmer wrote:
> >In response to ramasubramanian :
> >
> >Please, create a new mail for a new topic and don't hijack other
> >threads.
>
> Even more so - this isn't probably the right mai
In response to ramasubramanian :
Please, create a new mail for a new topic and don't hijack other
threads.
> Hi all,
>I have a table emp. using where condition can i get the result
> prioritized.
> Take the example below.
>
> select ENAME,ORIG_SALARY from employee where (ename='Tom' and
>
In response to tom :
> Hi,
>
> === Problem ===
>
> i have a db-table "data_measurand" with about 6000 (60 Millions)
> rows and the following query takes about 20-30 seconds (with psql):
>
> mydb=# select count(*) from data_measurand;
> count
> --
> 60846187
> (1 row)
>
>
> ==
In response to Bob Dusek :
> Hello,
>
> We're running Postgres 8.4.2 on Red Hat 5, on pretty hefty hardware...
>
> 4X E7420 Xeon, Four cores (for a total of 16 cores)
> 2.13 GHz, 8M Cache, 1066 Mhz FSB
> 32 Gigs of RAM
> 15 K RPM drives in striped raid
>
> Things run fine, but when we get a lot
In response to Lefteris :
> Hi Arjen,
>
> so I understand from all of you that you don't consider the use of 25k
> for sorting to be the cause of the slowdown? Probably I am missing
> something on the specific sort algorithm used by PG. My RAM does fill
> up, mainly by file buffers from linux, but
In response to Lefteris :
> Thank you all for your answers!
>
> Andrea, I see the other way around what you are saying:
>
> Sort (cost=7407754.12..7407754.13 rows=4 width=2) (actual
> time=371188.821..371188.823 rows=7 loops=1)
> Seq Scan on ontime (cost=0.00..7143875.40 rows=52775727 width=2)
In response to Lefteris :
>
> airtraffic=# EXPLAIN ANALYZE SELECT "DayOfWeek", count(*) AS c FROM
> ontime WHERE "Year" BETWEEN 2000 AND 2008 GROUP BY "DayOfWeek" ORDER
> BY c DESC;
>QUERY
> PLAN
>
In response to ramasubramanian :
> Dear all,
> The query is slow when executing in the stored procedure(it is taking
> around 1 minute). when executing as a sql it is taking 4 seconds.
> basically i am selecting the varchar column which contain 4000 character. We
> have as iindex on the table.
In response to Lorenzo Allegrucci :
>
> Hi all,
>
> I'm experiencing a strange behavior with my postgresql 8.3:
> performance is degrading after 3/4 days of running time but if I
> just restart it performance returns back to it's normal value..
> In normal conditions the postgres process uses abo
In response to Waldomiro :
> I?m thinking It hapens because in the 7 millions tables, the same 8k
> block has diferent records with different keys, so only a few records
> with 'my_key' is retrieved when I read a 8k block.
> In the part_table, all records stored in a 8k block have 'my_key', so
>
In response to Shaul Dar :
> Hi,
>
> I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK -->
> T1.PK, possibly multiple T2 rows may reference the same T1 row). I have
> deleted
> about 2/3 of table T2. I now want to delete all rows in T1 that are not
> referenced by T2, i.e
In response to Michal Vitecek :
> There are ~100 tables in the database and one of them (tableOne) always
> contains only a single row. There's one index on it. However performing
In this case, only one row, you don't need an index. Really.
> update on the single row (which occurs every 60 sec
In response to Vratislav Benes :
> but when I try make a condition by join table, the query plan is not optimal:
>
>
> select period_id from periods where y=2009 and w=14;
> period_id
> ---
>704
> (1 row)
>
>
> explain select sum(s_pcs),sum(s_val)
> from data d inner join perio
In response to ramasubramanian :
> Dear all,
>Can we create a trigger on particular column of a table?
No, but you can compare OLD.column and NEW.column and return from the
function if NEW.column = OLD.column.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639
In response to Hari, Balaji :
> Hi,
>
>
>
> I am relatively new to PostgreSQL(8.1) and facing the following problem.
Sure? 8.1? Your explain looks like 8.2 or 8.3. 8.3 contains a
full-text-search.
If really not 8.3 you can use tsearch2, it is a contrib-module.
Andreas
--
Andreas Kretschmer
In response to Thomas Finneid :
>
> Hi I am developing a database and have a couple of questions I havent
> found an answer to yet.
>
> 1) how do I find the size of an index, i.e. the size on disk?
http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
http://andreas.scherbaum.la/b
In response to Jörg Kiegeland :
> Hi,
This list, [Perform], is obviously the wrong list for such...
>
> I want to store a boolean SQL condition in a column "condition_column"
> of my table "myTable".
> This condition refers to other columns of the same table and shall use
> one parameter, e.g.
am Fri, dem 05.12.2008, um 14:23:33 +0100 mailte Rogatzki Rainer folgendes:
> Hello Andreas,
>
> your hint did the trick - thank you very much!
glad to help you...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, priva
am Fri, dem 05.12.2008, um 11:41:11 +0100 mailte Rogatzki Rainer folgendes:
> -- Example with bad performance since index isn't used
> explain
> select c.id_country, sum(c.cost) as sum_cost
> from costs c, accounting_interval a
> where c.id_user = 123
> and c.id_state = 1
> and a.id
am Wed, dem 26.11.2008, um 21:21:04 -0700 mailte Kevin Kempter folgendes:
> Next we have a select count(*) that also one of the top offenders:
>
> select count(*) from public.tab3 where user_id=31
> and state='A'
> and amount>0;
>
> QUERY PLAN
am Sun, dem 23.11.2008, um 6:20:08 +0200 mailte Andrus folgendes:
> Gregory,
>
> > I would suggest running ANALYZE
> >idtellUued at some point before the problematic query.
>
> Thank you.
> After adding analyze all is OK.
> Is analyze command required in 8.3 also ?
Yes.
Andreas
--
Andreas
am Mon, dem 21.07.2008, um 15:20:27 -0300 mailte Leví Teodoro da Silva
folgendes:
> - In oracle he makes a full backup two times in a day. In this range of time,
> Oracle make a lot of mini-backups, but this backups is about just the data
> whose have changed in this time. If the system fails, he
am Mon, dem 14.07.2008, um 12:04:49 +0530 mailte Praveen folgendes:
>
> Hi All,
> I am having a trigger in table, If I update the the table manually trigger
> is firing immediately(say 200ms per row), But if I update the table through
> procedure the trigger is taking time to fire(say 7 to 1
am Mon, dem 19.05.2008, um 23:56:27 -0600 mailte kevin kempter folgendes:
> Hi all;
>
> I have a query that does this:
>
> update tab_x set (inactive_dt, last_update_dt) =
> ((select run_dt from current_run_date), (select run_dt from
> current_run_date))
> where
> cust_id::text || loc_id::text
am Wed, dem 23.04.2008, um 9:58:10 +0200 mailte A. Kretschmer folgendes:
> > Query A (two queries)
> >
> > select distinct moment.mid from moment,timecard where parent = 45 and
> > (pid=17 and timecard.mid = moment.mid) order by moment.mid;
> > select distinct m
am Wed, dem 23.04.2008, um 9:23:07 +0200 mailte Hans Ekbrand folgendes:
> I cannot understand why the following two queries differ so much in execution
> time (almost ten times)
wild guess: different execution plans.
Can you show us the plans? (EXPLAIN ANALYSE SELECT ...)
>
> Query A (two q
am Wed, dem 26.03.2008, um 16:15:20 +0100 mailte Marinos Yannikos folgendes:
> A. Kretschmer schrieb:
> >>create index foo1 on bla (a);
> >>create index foo2 on bla (b);
> >>create index foo3 on bla (a,b);
> >>[...]
> >
> >Which version do
am Wed, dem 26.03.2008, um 15:18:53 +0100 mailte Marinos Yannikos folgendes:
> Hello,
>
> we have several indexes such as:
>
> create index foo1 on bla (a);
> create index foo2 on bla (b);
> create index foo3 on bla (a,b);
>
> They are all used often by frequently used queries (according to
>
am Thu, dem 13.03.2008, um 18:54:18 +0530 mailte sathiya psql folgendes:
> Is there any tool to draw ER diagram from SQL schema file...
>
14:31 < akretschmer> ??erd
14:31 < rtfm_please> For information about erd
14:31 < rtfm_please> see http://druid.sf.net/
14:31 < rtfm_please> or http://schemas
am Thu, dem 13.03.2008, um 12:51:54 +0530 mailte petchimuthu lingam folgendes:
>
> I have created partial index on a field with conditions, if field _a > 200
> and field_a < 300.
>
> I using the select query with condition as field_a in ( 100, 250, 289, ),
>
> Will it use the index.
Why do
am Thu, dem 06.03.2008, um 18:13:50 +0530 mailte sathiya psql folgendes:
>
> Yes it is the latest stable version.
>
>
> is there any article saying the difference between this 7.3 and 8.4
http://developer.postgresql.org/pgdocs/postgres/release.html
Andreas
--
Andreas Kretschmer
Kontakt:
am Thu, dem 06.03.2008, um 12:36:48 +0530 mailte sathiya psql folgendes:
>
> QUERY PLAN
> --
> Aggregate (cost=20
am Thu, dem 06.03.2008, um 12:17:55 +0530 mailte sathiya psql folgendes:
> TRIGGER i can use if i want the count of the whole table, but i require for
> some of the rows with WHERE condition
>
> so how to do that ???
Okay, in this case a TRIGGER are a bad idea. You can use an INDEX on
this r
am Thu, dem 06.03.2008, um 12:13:17 +0530 mailte sathiya psql folgendes:
> is there any way to explicitly force the postgres to use index scan
Not realy, PG use a cost-based optimizer and use an INDEX if it make
sense.
>
> On Thu, Mar 6, 2008 at 12:06 PM, A. Kretschmer <
> [
am Thu, dem 06.03.2008, um 1:26:46 -0500 mailte Mark Mielke folgendes:
>
>
> There aren't a general solution. If you realy need the exact count of
> tuples than you can play with a TRIGGER and increase/decrease the
> tuple-count for this table in an extra table.
>
>
>
am Thu, dem 06.03.2008, um 11:13:01 +0530 mailte sathiya psql folgendes:
> count(*) tooks much time...
>
> but with the where clause we can make this to use indexing,... what where
> clause we can use??
An index without a WHERE can't help to avoid a seq. scan.
>
> Am using postgres 7.4 in Deb
am Mon, dem 18.02.2008, um 14:41:50 +0530 mailte Kathirvel, Jeevanandam
folgendes:
> Hi,
>
> I want to disable Write Ahead Log (WAL) completely because of
>
>
> Please give your inputs, to resolve this issue..
Change the destination for this log to /dev/null
Andreas
--
Andrea
am Mon, dem 23.07.2007, um 19:24:48 +0200 mailte Paul van den Bogaard
folgendes:
> the manual somewhere states "... if archiving is enabled..." To me
Please don't hijack other threads...
(don't edit a mail-subject to create a new thread. Create a NEW mail!)
Andreas
--
Andreas Kretschmer
Ko
am Wed, dem 11.07.2007, um 22:19:58 +0200 mailte Andreas Kretschmer folgendes:
> > Also, part of the problem here looks to be an overestimate of the number
> > of rows matching ab = 347735. It might help to increase the statistics
> > target for that column.
>
> I will try this tomorrow and info
am Wed, dem 11.07.2007, um 14:52:01 -0400 mailte Alex Deucher folgendes:
> >Okay, i got a really different plan, but i expected _NOT_ a
> >performance-boost like this. I expected the opposite.
> >
> >
> >It's not a really problem, i just played with this. But i'm confused
> >about this...
> >
>
>
am Thu, dem 28.06.2007, um 16:16:50 +1000 mailte Chris folgendes:
> Is there a better way to write the update? I thought about something
> like this (but couldn't get it working - guess I don't have the right
> syntax):
>
> update t1 set domainname=(select id, SUBSTRING(emailaddress FROM
> POS
am Thu, dem 28.06.2007, um 15:03:32 +1000 mailte Chris folgendes:
> Hi all,
>
> I'm trying to do an update of a reasonably large table and it's taking
> way too long so I'm trying to work out why and if I need to tweak any
> settings to speed it up.
>
> The table is around 3.5 million records.
am Tue, dem 19.06.2007, um 13:12:58 +0530 mailte soni de folgendes:
> Hello,
>
> We have installed postgres 8.2.0
>
> default time zone which postgres server using is
>
> template1=# SHOW timezone;
> TimeZone
> ---
> ETC/GMT-5
> (1 row)
>
>
> But we want to set this timezone pa
am Fri, dem 08.06.2007, um 1:22:14 -0700 mailte choksi folgendes:
> Hi all,
>
> I had a database which uses to hold some 50 Mill records and disk
> space used was 103 GB. I deleted around 34 Mill records but still the
> disk size is same. Can some on please shed some light on this.
DELETE only
am Mon, dem 28.05.2007, um 8:45:38 -0400 mailte Dave Cramer folgendes:
> Since PITR has to enable archiving does this not increase the amount
> of disk I/O required ?
Yes. But you can use a different hard drive for this log.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D
am Wed, dem 07.03.2007, um 12:13:55 +0530 mailte Gauri Kanekar folgendes:
> Hi List,
>
> Can i find out the timestamp when last a record from a table got updated.
> Do any of the pg system tables store this info.
No, impossible. But you can write a TRIGGER for such tasks.
Andreas
--
Andreas
am Mon, dem 05.03.2007, um 20:25:21 +0530 mailte Ravindran G-TLS,Chennai.
folgendes:
> Dear All,
>
> I have to take backup of a database as a SQL file using the pg_dump utility
> and
> I have to check the disk space
>
> before taking the backup. Hence I need to estimate the size of the pg_dump
am Tue, dem 23.01.2007, um 13:34:19 +0100 mailte Laurent Manchon folgendes:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 80 rows:
>
> select count(*)from tbl;
PLEASE READ THE ANSWERS FOR YOUR OTHER MAILS.
Andreas
--
Andreas Krets
am Tue, dem 23.01.2007, um 11:34:52 +0100 mailte Laurent Manchon folgendes:
> Hi,
>
> I have a slow response of my PostgreSQL database 7.4 using this query below
> on a table with 80 rows:
>
> select count(*)from tbl;
If i remember correctly, i saw this question yesterday on an other
list..
am Mon, dem 11.12.2006, um 19:41:29 +0530 mailte Ravindran G - TLS, Chennai.
folgendes:
> Thank you very much for your reply.
>
> This is not working in Postgresql 8.1.4. Its throwing some error.
Which errors?
test=# show geqo_threshold;
geqo_threshold
12
(1 row)
test=*#
am Tue, dem 05.12.2006, um 13:02:06 -0500 mailte Tom Lane folgendes:
> In 8.0 that might be counterproductively high --- we have seen cases
> where more sort_mem = slower with the older sorting code. I concur
> with Luke's advice that you should update to 8.2 (not 8.1) to get the
> improved sorti
am Fri, dem 03.11.2006, um 3:12:14 -0800 mailte Drew Wilson folgendes:
> I have 700 lines of non-performant pgSQL code that I'd like to
> profile to see what's going on.
>
> What's the best way to profile stored procedures?
RAISE NOTICE, you can raise the aktual time within a transaction with
am Tue, dem 17.10.2006, um 17:09:29 +0530 mailte soni de folgendes:
> I didn't understand the "Bitmap Scan" and the sentence "indexes will be
> dynamically converted to bitmaps in memory". What does mean by "Bitmap Scan"
> in
> database?
For instance, you have a large table with 5 indexes on thi
am Tue, dem 17.10.2006, um 11:33:18 +0200 mailte Ruben Rubio folgendes:
> >
> > SELECT max(idcomment)
> > FROM ficha vf
> > INNER JOIN comment c ON (vf.idficha=c.idfile AND (idestado=3 OR
> > idestado=4))
> > WHERE idstatus=3
> > AND ctype=1
check for indexes on vf.idficha, c.idfile, idstatu
am Tue, dem 29.08.2006, um 16:55:11 +0200 mailte Willo van der Merwe folgendes:
> >>4 1/2 seconds for a count(*) ? This seems a bit rough - is there anything
> >>else
> >>
> >
> >Because of MVCC.
> >http://www.thescripts.com/forum/thread173678.html
> >http://www.varlena.com/GeneralBits/120.ph
am Tue, dem 29.08.2006, um 15:52:50 +0200 mailte Willo van der Merwe folgendes:
> and it has 743321 rows and a explain analyze select count(*) from
> property_values;
> QUERY
> PLAN
>
am Tue, dem 29.08.2006, um 12:51:27 +0530 mailte Vanitha Jaya folgendes:
> Hi Friends,
>
> I have one doubt in LIMIT & OFFSET clause operation.
> I have a table "test_limit", and it contain,
First of all, you can use EXPLAIN ANALYSE for such tasks!
test=*# explain analyse select * from
am Mon, dem 28.08.2006, um 19:30:44 +0530 mailte Ravindran G - TLS, Chennai.
folgendes:
> Thanks Alvaro.
>
> We are using PostgreSQL 7.1 cygwin installed on Windows 2000.
*grrr*
>
> We understand that the maximum connections that can be set is 64 in
> Postgresql 7.1 version.
>
> But our ap
am 06.07.2006, um 9:40:16 +0300 mailte Eugeny N Dzhurinsky folgendes:
> In postgresql.conf I have these settings:
>
> shared_buffers = 4
> work_mem = 8192
> maintenance_work_mem = 16384
> max_stack_depth = 2048
>
> all other settings are left by default (except ones needed for pg_autovacuum
am 16.06.2006, um 15:58:46 +0900 mailte David Leangen folgendes:
>
> Hello!
>
> I am trying to delete an entire table. There are about 41,000 rows in
> the table (based on count(*)).
>
> I am using the SQL comment: delete from table;
Use TRUNCATE table.
Andreas
--
Andreas Kretschmer(Kon
am 15.06.2006, um 14:34:51 +0800 mailte [EMAIL PROTECTED] folgendes:
>
>
>
>
> so what is the best way to implement two databases in one machine?
> implement with two postgresql instances with separate directory or
> implement under one instance?
What do you want to do?
Do you need 2 separate
am 15.06.2006, um 13:58:20 +0800 mailte [EMAIL PROTECTED] folgendes:
>
>
>
>
> Hi,
>
> Is it possible to start two instances of postgresql with different port and
> directory which run simultaneously?
Yes, this is possible, and this is the Debian way for updates.
> If can then will this ca
am 30.05.2006, um 17:47:47 -0300 mailte [EMAIL PROTECTED] folgendes:
> Hi,
>
> Is there a command to Insert a record If It does not exists and a update if
> It exists?
Not a single command, but a solution:
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-E
77 matches
Mail list logo
