On 4 April 2014 21:26, PARIS Nicolas wrote:
> this postgres documentation :
> http://www.postgresql.org/docs/9.3/static/ecpg-connect.html
> says it is actually possible to manage connection in C stored procedure.
>
> I may be wrong...
That page doesn't refer to triggers at all, so I'm still not s
On 4 April 2014 21:07, PARIS Nicolas wrote:
> Ok thanks,
>
> And what about triggers. 8 triggers based on the same event won't be
> multithreaded ?
I'm not clear on how triggers come into this. You can't have triggers
on materialized views, and they don't fire triggers on tables or views
that th
On 4 April 2014 20:49, PARIS Nicolas wrote:
> Thanks,
>
> "The only thing that immediately comes to mind would be running a
> rather hacky DO function in 4 separate sessions:"
> You mean 8 sessions I guess.
Yes, typo.
> 8 separate sessions ?
> Have you any idea how to manage sessions ? Is it po
On 4 April 2014 17:29, Nicolas Paris wrote:
> Hello,
>
> My question is about multiprocess and materialized View.
> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
> I (will) have something like 3600 materialised views, and I would like to
> know the way to refresh them i
On 18 October 2012 17:52, Tom Lane wrote:
> Thom Brown writes:
>> On 18 October 2012 17:44, Tom Lane wrote:
>>> Thom Brown writes:
>>>> And as a side note, how come it's impossible to get the planner to use
>>>> an index-only scan to satisfy t
On 18 October 2012 18:00, Peter Geoghegan wrote:
> On 18 October 2012 17:52, Tom Lane wrote:
>> I forgot to mention that there is a klugy workaround: add the required
>> variable(s) as extra index columns. That is,
>>
>> create index i on t (foo(x), x);
>
> Is there a case to be made for
On 18 October 2012 17:44, Tom Lane wrote:
> Thom Brown writes:
>> And as a side note, how come it's impossible to get the planner to use
>> an index-only scan to satisfy the query (disabling sequential and
>> regular index scans)?
>
> Implementation restriction -
On 18 October 2012 17:24, Peter Geoghegan wrote:
> On 18 October 2012 17:11, Thom Brown wrote:
>> The estimate is down to almost a 10th of what it was before. What's going
>> on?
>
> Even though the index isn't used, the pg_statistic entries that the
>
On 18 October 2012 17:11, Thom Brown wrote:
> Hi all,
>
> I've created a test table containing 21 million random dates and
> times, but I get wildly different results when I introduce a
> functional index then ANALYSE again, even though it doesn't use the
> index:
>
Hi all,
I've created a test table containing 21 million random dates and
times, but I get wildly different results when I introduce a
functional index then ANALYSE again, even though it doesn't use the
index:
postgres=# CREATE TABLE test (id serial, sampledate timestamp);
CREATE TABLE
postgres=#
On 23 February 2012 17:35, Alessandro Gagliardi wrote:
> I should have been more clear. I virtually never delete or do updates, but I
> insert a lot. So the table does change quite a bit, but only in one
> direction.
The same thing applies. VACUUM cleans up dead tuples, which INSERTs
don't creat
On 22 February 2012 23:50, Alessandro Gagliardi wrote:
> I have a database where I virtually never delete and almost never do
> updates. (The updates might change in the future but for now it's okay to
> assume they never happen.) As such, it seems like it might be worth it to
> set autovacuum=off
only appropriate when there is no access to table
data from within the function. An example of IMMUTABLE functions
would be mathematical operations, where only the inputs and/or
function constants are used to produce a result.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registere
d an index containing data records make sense?
Yes, it's called a covering index, where the data required to produce
results for the query are entirely contained in the index. That
should be hopefully coming in 9.2.
See http://wiki.postgresql.org/wiki/Index-only_scans
--
Thom Brown
Tw
On 7 April 2011 08:10, Thom Brown wrote:
> On 7 April 2011 07:37, Tom Lane wrote:
>> Thom Brown writes:
>>> On 6 April 2011 05:44, Tom Lane wrote:
>>>> It looks like the index predicate expression isn't getting the right
>>>> collation assigned
On 7 April 2011 07:37, Tom Lane wrote:
> Thom Brown writes:
>> On 6 April 2011 05:44, Tom Lane wrote:
>>> It looks like the index predicate expression isn't getting the right
>>> collation assigned, so predtest.c decides the query doesn't imply the
>>&
On 6 April 2011 00:02, Kenneth Marshall wrote:
> The stats seem off. Are you certain that an analyze has run?
>
> Cheers,
> Ken
>
Yes, an ANALYZE was definitely run against the table.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
On 6 April 2011 05:44, Tom Lane wrote:
> Thom Brown writes:
>> The index doesn't get used. There's probably a logical explanation,
>> which is what I'm curious about.
>
> Er ... it's broken?
>
> It looks like the index predicate expression i
q Scan on indextest (cost=0.00..143386.48 rows=5606 width=9)
(actual time=164.321..1299.794 rows=8000 loops=1)
Filter: (stuff = 'bark'::text)
Total runtime: 1300.267 ms
(3 rows)
The index doesn't get used. There's probably a logical explanation,
which is what I'm curi
On 2 March 2011 19:52, Robert Haas wrote:
> On Wed, Mar 2, 2011 at 9:11 AM, Thom Brown wrote:
>> On 2 March 2011 19:38, Robert Haas wrote:
>>> On Thu, Feb 24, 2011 at 11:38 AM, Dave Johansen
>>> wrote:
>>>> I also just noticed that an ORDER BY x LIMIT
because the ORDER BY and LIMIT would need to be applied to the
>> subqueries and then re-applied after the APPEND,
>
> PostgreSQL 9.1 will know how to do this, FWIW.
Out of curiosity, what was the commit for this?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Register
esql.org/docs/8.3/static/release-8-3.html#AEN87319
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
lified and also that
> there are many implementation details and difficulties that I am not
> aware.
>
> I strongly believe that the outcome of the discussion regarding this
> issue will be helpful.
>
>
Which version of PostgreSQL are you basing this on?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
tain? For instance, if you have 10 columns each returning massive XML
documents, each hundreds of megs, the bottleneck would be I/O bandwidth.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
; > The server will just boot, load data, run, hopefully not crash but if
> > it would, just start over with load and run.
>
> If you want fast read queries then changing
> fsync/full_page_writes/synchronous_commit won't help you.
Yes, those will be for write-performance
On 5 November 2010 11:14, Thom Brown wrote:
> On 5 November 2010 10:59, A B wrote:
>
>> Hi there.
>>
>> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>> care for your data (you accept 100% dataloss and datacorruption if any
>> error sho
full_page_writes (i.e. running with scissors).
Also depends on what you mean by "as fast as possible". Fast at doing
what? Bulk inserts, selecting from massive tables?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
26 loops=1)
> Total runtime: 85875.591 ms
> (13 rows)
If you look at your latest explain, it shows that it's merging the
results of a full sequential scan of both crm and crm_active. Is
crm_active a child table of crm?
Do you no longer have the index "crm_pkey" on the parent tab
this thread:
> http://www.jitterbit.com/PhpBB/viewtopic.php?f=5&t=869
>
> The specs of postres environment are:
>
> Windows XP SP3
> PostgreSQL 9.0.0, compiled by Visual C++ build 1500, 32-bit
> pgAdmin III 1.12.0 (Sep17 2010, rev: REL-1_12_0)
> PostGIS 2.0SVN
>
the list.
>
I don't think PgAdmin will display indexes created by primary keys,
only if indisprimary is false.
--
Thom Brown
Registered Linux user: #516935
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
ex to your views column to prevent
the need for a sequential scan to sort. Also, ILIKE won't be able to
use an index, so if you wish to match against title, you may wish to
change your query to use:
WHERE lower(title) LIKE
And then create an index on lower(title).
Regards
--
T
On 10 August 2010 17:06, Thom Brown wrote:
> On 10 August 2010 17:03, Matthew Wakeling wrote:
>> On Tue, 10 Aug 2010, Thomas Kellerer wrote:
>>>
>>> No. It's built in (8.4) and it's called Windowing functions:
>>> http://www.postgresql.org
The last_value function is a window
function aggregate. Give it a try.
--
Thom Brown
Registered Linux user: #516935
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On 15 July 2010 15:41, Patrick Donlin wrote:
> I have two servers with equal specs, one of them running 8.3.7 and the new
> server running 8.4.4. The only tweak I have made from the default install
> (from Ubuntu repositories) is increasing shared_buffers to 768MB. Both
> servers are running 64-bi
On 12 July 2010 14:11, Rob Wultsch wrote:
> On Mon, Jul 12, 2010 at 4:15 AM, A. Kretschmer
> wrote:
>> 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 r
On 1 July 2010 06:19, Srikanth Kata wrote:
>
> Please tell me What is the best way to optimize this query
>
> select
> s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr
> as svcdescr from vwsubsmin s inner join packages p on s.svcno=p.pkgno inner
> join
> account a on a.actno
On 31 March 2010 15:23, Bruce Momjian wrote:
> James Mansion wrote:
>> Hannu Krosing wrote:
>> > Pulling the plug should not corrupt a postgreSQL database, unless it was
>> > using disks which lie about write caching.
>> >
>> Didn't we recently put the old wife's 'the disks lied' tale to bed in
>>
On 28 May 2010 19:54, Tom Lane wrote:
> Thom Brown writes:
>> I get this:
>
>> Limit (cost=0.00..316895.11 rows=400 width=211) (actual
>> time=3.880..1368.936 rows=400 loops=1)
>> -> GroupAggregate (cost=0.00..41843621.95 rows=52817 width=211)
>> (act
I'm using PostgreSQL 9.0 beta 1. I've got the following table definition:
# \d parts_2576
Table "public.parts_2576"
Column | Type |
Modifiers
++--
2010/5/25 Joachim Worringen :
> Am 25.05.2010 10:49, schrieb Grzegorz Jaśkiewicz:
>>
>> temporary tables are handled pretty much like the regular table. The
>> magic happens on schema level, new schema is setup for connection, so
>> that it can access its own temporary tables.
>> Temporary tables a
On 20 May 2010 20:02, Tom Lane wrote:
> Thom Brown writes:
>> On 20 May 2010 17:36, David Jarvis wrote:
>> Okay, get your app to convert the month-date to a day of year, so we
>> have year_start, year_end, day_of_year_start, day_of_year_end
>
>> and your where cl
On 20 May 2010 19:36, Thom Brown wrote:
> On 20 May 2010 17:36, David Jarvis wrote:
>> Hi, Thom.
>>
>> The query is given two items:
>>
>> Range of years
>> Range of days
>>
>> I need to select all data between the range of days (e.g., Dec
On 20 May 2010 17:36, David Jarvis wrote:
> Hi, Thom.
>
> The query is given two items:
>
> Range of years
> Range of days
>
> I need to select all data between the range of days (e.g., Dec 22 - Mar 22)
> over the range of years (e.g., 1950 - 1970), such as shown here:
>
> http://i.imgur.com/MUkuZ
On 20 May 2010 06:06, David Jarvis wrote:
> Hi,
>
> I recently switched to PostgreSQL from MySQL so that I can use PL/R for data
> analysis. The query in MySQL form (against a more complex table structure)
> takes ~5 seconds to run. The query in PostgreSQL I have yet to let finish,
> as it takes o
2010/5/14 Piotr Legiecki
> Hi
>
> I have a situation at my work which I simply don't understand and hope
> that here I can find some explanations.
>
> What is on the scene:
> A - old 'server' PC AMD Athlon64 3000+, 2GB RAM, 1 ATA HDD 150GB, Debian
> etch, postgresql 8.1.19
> B - new server HP DL
On 18 March 2010 14:31, Corin wrote:
> Hi all,
>
> I'm running quite a large social community website (250k users, 16gb
> database). We are currently preparing a complete relaunch and thinking about
> switching from mysql 5.1.37 innodb to postgresql 8.4.2. The database server
> is a dual dualcore
On 18 February 2010 12:18, A. Kretschmer
wrote:
> 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
>
> On Thu, Feb 18, 2010 at 6:00 PM, Thom Brown wrote:
>>
>> 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
>> >
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 like operation?
> Anyone please help.
>
How ma
On 9 February 2010 21:46, Dimi Paun wrote:
> >From what I've read on the net, these should be very similar,
> and should generate equivalent plans, in such cases:
>
> SELECT DISTINCT x FROM mytable
> SELECT x FROM mytable GROUP BY x
>
> However, in my case (postgresql-server-8.1.18-2.el5_4.1),
> t
Had a quick look at a benchmark someone put together of MySQL vs PostgreSQL,
and while PostgreSQL is generally faster, I noticed the bulk delete was very
slow: http://www.randombugs.com/linux/mysql-postgresql-benchmarks.html
Is this normal?
Thom
2009/12/8 Schmitz, David
> Hi Andres,
>
> EXPLAIN ANALYZE
> select ah.ORDER8_ID, ah.BUILTUP_ID, rl.LINK_ID, la.SIDE,
>rl.ROAD_NAME_ID, rl.LEFT_ADDRESS_RANGE_ID,
> rl.RIGHT_ADDRESS_RANGE_ID,
>rl.IS_EXIT_NAME, rl.EXPLICATABLE, rl.IS_JUNCTION_NAME,
>
2009/12/7 Kevin Grittner
> Ben Brehmer wrote:
>
> > -7.5 GB memory
> > -4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units
> >each)
> > -64-bit platform
>
> What OS?
>
> > (PostgreSQL 8.1.3)
>
> Why use such an antiquated, buggy version? Newer versions are
> faster.
>
> -Kevin
>
2009/11/24 Luca Tettamanti
> On Tue, Nov 24, 2009 at 3:59 PM, Jerry Champlin
> wrote:
> > You may want to consider using partitioning. That way you can drop the
> > appropriate partition and never have the overhead of a delete.
>
> Hum, I don't think it's doable in my case; the partitioning is
2009/11/23 Faheem Mitha
>
> Hi everybody,
>
> I've got two queries that needs optimizing. Actually, there are others, but
> these are pretty representative.
>
> You can see the queries and the corresponding plans at
>
> http://bulldog.duhs.duke.edu/~faheem/snpdb/opt.pdf
>
> or
>
> http://bulldog.
2009/11/22 Jonathan Blitz
> I have a table with a number of columns.
>
> I perform
>
> Select *
> from table
> order by a,b
>
> There is an index on a,b which is clustered (as well as indexes on a and b
> alone).
> I have issued the cluster and anyalze commands.
>
> Nevertheless, PostgreSQL perf
2009/11/20 Richard Neill
>
>
> It might also help if you posted your postgresql.conf too.
>>
>
> Below (have removed the really non-interesting bits).
>
> Thanks,
>
> Richard
>
>
> I can't actually see anything in your config that would cause this problem.
:/
As for seeing the progress of an upd
2009/11/20 Richard Neill
>
>
> Thom Brown wrote:
> >
>
>> It looks like your statistics are way out of sync with the real data.
>>
>> > Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual
>> time=248577.879..253168.466 rows=347308 l
2009/11/20 Richard Neill
>
>> Greg Williamson wrote:
>>
>>> Richard --
>>>
>>> You might post the results of "EXPLAIN ANALYZE ;" ... be
>>> sure to run it in a transaction if you want to be able roll it back. Perhaps
>>> try "EXPLAIN ;" first as it is faster, but EXPLAIN ANALYZE shows
>>> what t
2009/10/14 Scott Marlowe :
>
> If what you're trying to do is emulate a real world app which randomly
> grabs rows, then you want to setup something ahead of time that has a
> pseudo random order and not rely on using anything like order by
> random() limit 1 or anything like that. Easiest way is
60 matches
Mail list logo
