On Wed, 19 Nov 2008, [ISO-8859-5] ??? wrote:
> Query 1:
> select * from t1 where id not in (select id from t2);
>
> Query 2 (gives same result as Q1):
> select * from t1 except all (select id from t2);
It gives the same result as long as no nulls are in either table. If
either table
On Sat, 21 Apr 2007, cluster wrote:
> I have investigated a bit now and found the following:
>
> When I perform the update the *first* time, the triggers are actually
> not evaluated. But from the second update they are.
Are these in one transaction? If so, then right now after the first
update,
On Thu, 1 Mar 2007, Rob Schall wrote:
> There are 4 entries (wanted to make the playing field level for this
> test). There are 2 with true for istf and 2 with false.
Then analyzing might help, because I think it's estimating many more rows
for both cases, and with 2 rows estimated to be returned
On Thu, 1 Mar 2007, Rob Schall wrote:
> Question for anyone...
>
> I tried posting to the bugs, and they said this is a better question for here.
> I have to queries. One runs in about 2 seconds. The other takes upwards
> of 2 minutes. I have a temp table that is created with 2 columns. This
> tab
On Wed, 6 Dec 2006, Rafael Martinez wrote:
> We are having some problems with an UPDATE ... FROM sql-statement and
> pg-8.1.4. It takes ages to finish. The problem is the Seq Scan of the
> table 'mail', this table is over 6GB without indexes, and when we send
> thousands of this type of statement,
On Thu, 10 Aug 2006, Sue Fitt wrote:
> Hi all,
>
> This is my first post to the performance list, I hope someone can help me.
>
> I'm setting up a table with 2 columns, both of which reference a column
> in another table:
>
> CREATE TABLE headwords_core_lexemes (
> core_id int REFERENCES headwords
On Sun, 2 Apr 2006, Jan Kesten wrote:
> Stephan Szabo schrieb:
>
> > Did you reset the table contents between these two (remember that
> > explain analyze actually runs the query)? The second appears to be
> > changing no rows from the output.
>
> I for myself di
On Fri, 31 Mar 2006, Jan Kesten wrote:
>
> Hi folks!
>
> I have just a issue again with unused indexes. I have a database with a
> couple of tables and I have to do an sync job with them. For marking
> which row has to be transfered I added a new column token (integer, I
> will need some more toke
On Wed, 22 Feb 2006, ryan groth wrote:
> Does this work:
>
> "Merge Left Join (cost=0.00..2656.36 rows=6528 width=1522) (actual
> time=0.057..123.659 rows=6528 loops=1)"
> " Merge Cond: ("outer".uid = "inner".uid)"
> " -> Merge Left Join (cost=0.00..1693.09 rows=6528 width=1264)
> (actual tim
On Wed, 8 Feb 2006, Jacob Costello wrote:
> Postgres doesn't seem to optimize away unnecessary joins in a view
> definition when the view is queried in such a way that the join need not
> be executed. In the example below, I define two tables, foo and bar,
> with a foreign key on bar referencing
On Wed, 18 Jan 2006 [EMAIL PROTECTED] wrote:
> I have the answer I've been looking for and I'd like to share with all.
> After help from you guys, it appeared that the real issue was using an index
> for my order by X DESC clauses. For some reason that doesn't make good
> sense, postgres doesn't s
On Tue, 17 Jan 2006 [EMAIL PROTECTED] wrote:
> I created the index like this:
>
> CREATE INDEX rcbee_idx
> ON detail_summary
> USING btree
> (receipt, carrier_id, batchnum, encounternum, encounter_id);
>
> Is this correct ?
That would work if you were asking for all the columns ascending o
On Tue, 17 Jan 2006, Josh Berkus wrote:
> J,
>
> > I have an index built for each of these columns in my order by clause.
> > This query takes an unacceptable amount of time to execute. Here are the
> > results of the explain:
>
> You need a single index which has all five columns, in order.
I t
On Tue, 10 Jan 2006, Andrea Arcangeli wrote:
> I see. I can certainly fix it by stopping using LIKE. But IMHO this
> remains a bug, since until the statistics about the numberof matching
> rows isn't estimated well, you should not make assumptions on LIKE/NOT
> LIKE. I think you can change the cod
On Sun, 27 Nov 2005, Luke Lonergan wrote:
> Stephan,
>
> On 11/27/05 7:48 AM, "Stephan Szabo" <[EMAIL PROTECTED]> wrote:
>
> > On Sun, 27 Nov 2005, Luke Lonergan wrote:
> >
> >> Has anyone done the math.on the original post? 5TB takes how long to
On Sun, 27 Nov 2005, Luke Lonergan wrote:
> Has anyone done the math.on the original post? 5TB takes how long to
> scan once? If you want to wait less than a couple of days just for a
> seq scan, you'd better be in the multi-gb per second range.
Err, I get about 31 megabytes/second to do 5TB in
On Wed, 9 Nov 2005, Ashok Agrawal wrote:
> I noticed outer join is very very slow in postgresql as compared
> to Oracle.
>
> SELECT a.dln_code, a.company_name,
> to_char(a.certificate_date,'DD-MON-'),
> to_char(a.certificate_type_id, '99'),
> COALESCE(b.certificate_type_description,'None') ,
>
On Tue, 8 Nov 2005, Luke Lonergan wrote:
> Stephan,
>
> On 11/8/05 9:38 AM, "Stephan Szabo" <[EMAIL PROTECTED]> wrote:
>
> >> >
> >> > Just as we find with a similar comparison (with a "popular commercial,
> >> > proprietary da
On Tue, 8 Nov 2005, Luke Lonergan wrote:
> > SELECT tlid, min(ogc_fid)
> > FROM completechain
> > GROUP BY tlid
> > ORDER BY tlid;
> >
> > Even with this, it was more than a magnitude faster than Postgresql.
> > Which makes me think I have somehow misconfigured postgresql
> > (see the relevant par
On Fri, 16 Sep 2005, Martin Nickel wrote:
> Hello all,
> Mostly Postgres makes sense to me. But now and then it does something
> that boggles my brain. Take the statements below. I have a table
> (agent) with 5300 rows. The primary key is agent_id. I can do SELECT
> agent_id FROM agent and i
On Tue, 6 Sep 2005, Merlin Moncure wrote:
> Carlos wrote:
> SELECT * FROM SSIRRA where
> (YEAR = 2004 and CUSTOMER = 04 and CODE = 00 and PART >= 00) or
> (YEAR = 2004 and CUSTOMER = 04 and CODE > 00) or
> (YEAR = 2004 and CUSTOMER > 04) or
> (YEAR > 2004)
> [snip]
>
> ah,
On Fri, 26 Aug 2005, Mark Kirkwood wrote:
> However being a bit brutal:
>
> set enable_mergejoin=false;
> set enable_hashjoin=false;
>
> explain select c.id from c join b on c_id=c.id group by c.id order by
> c.id desc limit 5;
> QUERY PLAN
>
> ---
On Wed, 6 Jul 2005, Dario wrote:
>
> (first at all, sorry for my english)
> Hi.
>- Does "left join" restrict the order in which the planner must join
> tables? I've read about join, but i'm not sure about left join...
Yes. Reordering the outer joins can change the results in some cases which
On Mon, 4 Jul 2005, David Gagnon wrote:
> Thanks .. I miss that FK don't create indexed ... since Primary key
> implicitly does ...
>
> I'm a bit surprised of that behavior thought, since it means that if we
> delete a row from table A all tables (B,C,D) with FK pointing to this
> table (A) must
On Mon, 4 Jul 2005, David Gagnon wrote:
> If you can just help my understanding the choice of the planner.
>
> Here is the Query:
> explain analyse SELECT IRNUM FROM IR
> INNER JOIN IT ON IT.ITIRNUM = ANY ('{1000, 2000}') AND
> IT.ITYPNUM = 'M' AND IR.IRYPNUM = IT.ITYPNUM AND IR.IRNUM
On Thu, 21 Apr 2005, Enrico Weigelt wrote:
> I'm doing a simple lookup in a small table by an unique id, and I'm
> wondering, why explains tells me seqscan is used instead the key.
>
> The table looks like:
>
> idbigint primary key,
> a varchar,
> b varchar,
> c
On Mon, 18 Apr 2005, Joel Fradkin wrote:
> Another odd thing is when I tried turning off merge joins on the XP desktop
> It took 32 secs to run compared to the 6 secs it was taking.
> On the Linux (4proc box) it is now running in 3 secs with the mergejoins
> turned off.
>
> Unfortunately it takes
On Thu, 14 Apr 2005, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > ... At some point, if we can work out how to do all the semantics
> > properly, it'd probably be possible to replace the insert type check with
> > a per-statement check which woul
On Thu, 14 Apr 2005, Richard van den Berg wrote:
> Hello Chris,
>
> Thanks for your answers.
>
> Christopher Kings-Lynne wrote:
> > Deferring makes no difference to FK checking speed...
>
> But why then is the speed acceptable if I copy and then manually add the
> FK? Is the check done by the FK
On Tue, 29 Mar 2005, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > If there were some way to pass a "limit" into SPI_prepare that was treated
> > similarly to a LIMIT clause for planning purposes but didn't actually
> > change the outp
On Tue, 29 Mar 2005, Simon Riggs wrote:
> On Tue, 2005-03-29 at 10:31 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > If action is NO ACTION or RESTRICT then
> > > we need to SELECT at most 1 row that matches the criteria
> > > which means we can use LIMIT 1
> >
> > >
On Tue, 29 Mar 2005, Stephan Szabo wrote:
> On Tue, 29 Mar 2005, Simon Riggs wrote:
>
> > On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > > > Each value has 1/13th of the table, which is too many rows per value to
> > > > make an IndexScan an eff
On Tue, 29 Mar 2005, Simon Riggs wrote:
> On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > > Each value has 1/13th of the table, which is too many rows per value to
> > > make an IndexScan an efficient way of deleting rows from the table.
> >
> > But, the original question was that the
On Mon, 28 Mar 2005, Karim A Nassar wrote:
> On Mon, 28 Mar 2005, Simon Riggs wrote:
> > run the EXPLAIN after doing
> > SET enable_seqscan = off
>
> The results I previously supplied were searching for a non-existent
> value, so I have provided output for both cases.
>
> ***
> *** Searching f
On Sat, 26 Mar 2005, Karim Nassar wrote:
> On Sat, 2005-03-26 at 15:18 -0800, Stephan Szabo wrote:
> > On Sat, 26 Mar 2005, Karim Nassar wrote:
> >
> > > On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote:
> > > > That seems like it should be
On Sat, 26 Mar 2005, Karim Nassar wrote:
> On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote:
> > That seems like it should be okay, hmm, what does something like:
> >
> > PREPARE test(int) AS SELECT 1 from measurement where
> > id_int_sensor_meas_type = $1 FOR
On Fri, 25 Mar 2005, Karim Nassar wrote:
> On Fri, 2005-03-25 at 15:10 +, Simon Riggs wrote:
> > Karim: Did this happen? If not, can you drop and re-create and confirm
> > that you get the WARNING? If not, we have problems.
>
> No. Nor do I think that I should. SERIAL is shortcut for INTEGER,
On Fri, 25 Mar 2005, Simon Riggs wrote:
> On Fri, 2005-03-25 at 13:47 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > >>> Other than spec compliance, you mean? SQL99 says
> > >>>
> > >>> ... The declared type of each
On Fri, 25 Mar 2005, Simon Riggs wrote:
> On Fri, 2005-03-25 at 10:17 -0500, Tom Lane wrote:
> > Simon Riggs <[EMAIL PROTECTED]> writes:
> > > I vote to make this an ERROR in 8.1 - I see little benefit in allowing
> > > this situation to continue.
> >
> > Other than spec compliance, you mean? SQ
On Thu, 24 Mar 2005, Enrico Weigelt wrote:
> * Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > On Thu, Mar 24, 2005 at 02:32:48PM +0100, Enrico Weigelt wrote:
> >
> > > BTW: is it possible to explicitly clear the cache for immutable
> > > functions ?
> >
> > What cache? There is no caching of func
On Wed, 16 Mar 2005, David Gagnon wrote:
>
>
> Stephan Szabo wrote:
>
> >On Wed, 16 Mar 2005, David Gagnon wrote:
> >
> >
> >
> >>Hi
> >>
> >>
> >>
> >>>>I rerun the example with the debug info turned on in pos
On Wed, 16 Mar 2005, David Gagnon wrote:
> Hi
>
> >>I rerun the example with the debug info turned on in postgresl. As you
> >>can see all dependent tables (that as foreign key on table IC) are
> >>emptied before the DELETE FROM IC statement is issued. For what I
> >>understand the performance pr
On Wed, 9 Mar 2005, Michael McFarland wrote:
> I continue to be stumped by this. You are right that I should have
> listed the estimates provided by explain... basically for the select where
> bar = 41, it's estimating there will be 40,000 rows instead of 7, out of
> what's actuallly 5 millio
On Mon, 7 Mar 2005, Michael McFarland wrote:
>I'm trying to understand why a particular query is slow, and it seems
> like the optimizer is choosing a strange plan. See this summary:
>
>
> * I have a large table, with an index on the primary key 'id' and on a
> field 'foo'.
> > select count(*
On Fri, 25 Feb 2005, Vig, Sandor (G/FI-2) wrote:
> Hi,
>
> Another "funny" thing: I have a query which runs
> on (Linux) PostgreSQL 7.4.x under 10 sec. I tried
> to run it on (Windows) PostgreSQL 8.0 yesterday.
> It didn't finished at all! (I shoot it down after 10 minutes)
> I made various tests
On Mon, 14 Feb 2005, Ivan Voras wrote:
> Stephan Szabo wrote:
> > On Mon, 14 Feb 2005, Ivan Voras wrote:
>
> >>Could you give me an example for this, or point me to the relevant
> >>documentation?
>
> >
> > http://www.postgresql.org/docs/8.0/interact
On Mon, 14 Feb 2005, Ivan Voras wrote:
> Stephan Szabo wrote:
>
> > You can also create an index using a _pattern_ops operator
> > class which should be usable even with other collations.
>
> Could you give me an example for this, or point me to the relevant
> doc
On Mon, 14 Feb 2005, Ivan Voras wrote:
> PFC wrote:
> >
> > normally you shouldn't have to do anything, it should just work :
> >
> >> select field from table where field like 'abc%'
>
> > If it does not use the index, I saw on the mailing list that the
> > locale could be an issue.
>
> O
On Tue, 25 Jan 2005, Antony Paul wrote:
> Creating an index and using lower(column) does not change the explain
> plan estimates.
> It seems that it is not using index for like or ilike queries
> irrespective of whether it have a pattern matching character in it or
> not. (using PostgreSQL 7.3.3)
On Thu, 20 Jan 2005, Dan Langille wrote:
> On 20 Jan 2005 at 6:14, Stephan Szabo wrote:
>
> > On Wed, 19 Jan 2005, Dan Langille wrote:
> >
> > > Hi folks,
> > >
> > > Running on 7.4.2, recently vacuum analysed the three tables in
> > > qu
On Wed, 19 Jan 2005, Dan Langille wrote:
> Hi folks,
>
> Running on 7.4.2, recently vacuum analysed the three tables in
> question.
>
> The query plan in question changes dramatically when a WHERE clause
> changes from ports.broken to ports.deprecated. I don't see why.
> Well, I do see why: a seq
On Fri, 10 Dec 2004, Tomas [iso-8859-1] Skäre wrote:
> I have a table that looks like this:
>
> Table "public.cjm_object"
> Column | Type| Modifiers
> ---+---+---
> timestamp | bigint| not null
> jobid | bigint
On Sat, 4 Dec 2004, sarlav kumar wrote:
> Thanks for the information on replication tools!!
> Now, I have a question regarding locking tables and updating tables
> that have a relationship to the locked table.
>
> I opened up two pgsql windows logged in using same userid.
> Let's say I lock a tab
On Fri, 19 Nov 2004, Arshavir Grigorian wrote:
> Hi,
>
> I have a query that when run on similar tables in 2 different databases
> either uses the index on the column (primary key) in the where clause or
> does a full table scan. The structure of the tables is the same, except
> that the table whe
On Sat, 13 Nov 2004, vivek singh wrote:
> I am new to this group and postgresql. I am working on
> a project which uses postgresql and project is time
> critical. We did all optimization in our project but
> postgresql seems to be a bottle-neck. To solve this we
> run the database operations in a
On Thu, 21 Oct 2004, Victor Ciurus wrote:
> Hi all,
>
> I'm writing this because I've reached the limit of my imagination and
> patience! So here is it...
>
> 2 tables:
> 1 containing 27 million variable lenght, alpha-numeric records
> (strings) in 1 (one) field. (10 - 145 char lenght per record)
On Thu, 21 Oct 2004, Ray wrote:
> Hi All,
>
> I have a table in my postgres:
> Table: doc
> Column |Type | Modifiers
> ---+-+---
> doc_id | bigint | not null
> comp_grp_id | bigin
On Thu, 30 Sep 2004, Merlin Moncure wrote:
> OK, I have a situation that might be a performance problem, a bug, or an
> unavoidable consequence of using prepared statements. The short version
> is that I am getting function executions for rows not returned in a
> result set when they are in a pr
On Mon, 30 Aug 2004, Martin Sarsale wrote:
> On Mon, 2004-08-30 at 15:06, Merlin Moncure wrote:
> > create function is_somethingable (ctype, dtype) returns boolean as
>
> Thanks, but I would prefer a simpler solution.
>
> I would like to know why this uses a seqscan instead of an index scan:
>
> c
On Tue, 3 Aug 2004, Marius Andreiana wrote:
> I just vacuum analyse'd the database.
>
> Trying to run this query:
> EXPLAIN ANALYSE
> select * FROM trans
> WHERE query_id NOT IN (select query_id FROM query)
>
> but it will remain like that forever (cancelled after 30 min).
>
> My postgresql.conf
On Tue, 27 Jul 2004, Stan Bielski wrote:
> I having a great deal of difficulty getting postgres to do a hash join.
> Even if I disable nestloop and mergejoin in postgres.conf, the optimizer
> still refuses to select hash join. This behavior is killing my
> performance.
>
> Postgres version is 7.3.
On Tue, 27 Jul 2004, Merlin Moncure wrote:
> Greg Stark wrote:
> > > > do it for multi-column keys. It seems it would be nice if some
> syntax
> > > > similar to (a,b,c) > (a1,b1,c1) worked for this.
> > Hum. It would seem my intuition matches the SQL92 spec and Postgres
> gets
> > this
> > wrong
On Mon, 26 Jul 2004, Harmon S. Nine wrote:
> However, we can't get the planner to do an timestamp-based index scan.
>
> Anyone know what to do?
I'd wonder if the type conversion is causing you problems.
CURRENT_TIMESTAMP - INTERVAL '10 minutes' is a timestamp with time zone
while the column is t
On Tue, 20 Jul 2004, Stephan Szabo wrote:
>
> On Tue, 20 Jul 2004 [EMAIL PROTECTED] wrote:
>
> > I have (among other things) a parent table with 200 records and a child
> > table with 20MM or more. I set up referential integrity on the FK with
> > ON DELETE CASCADE.
&
On Tue, 20 Jul 2004 [EMAIL PROTECTED] wrote:
> I have (among other things) a parent table with 200 records and a child
> table with 20MM or more. I set up referential integrity on the FK with
> ON DELETE CASCADE.
>
> It appears that when a DELETE is done on the parent table, the child
> table del
On Wed, 7 Jul 2004, Joel McGraw wrote:
> However, this query performs a sequence scan on the table, ignoring the
> call_idx13 index (the only difference is the addition of the aspid field
> in the order by clause):
>
> elon2=# explain analyse select * from call where aspid='123C' and
> OpenedDateT
On Fri, 18 Jun 2004, [iso-8859-1] SZUCS Gábor wrote:
> Dear Gurus,
>
> - Original Message -
> From: "Stephan Szabo" <[EMAIL PROTECTED]>
> Sent: Thursday, June 10, 2004 7:14 PM
>
>
> >
> > On Thu, 10 Jun 2004, Stephan Szabo wrote:
>
On Thu, 10 Jun 2004, Stephan Szabo wrote:
>
> On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
>
> > I agree, but it should be a simple rewrite. No?
>
> It's NULLs inside the subselect that are the issue.
>
> select 1 in (select a from foo)
> select exists (
On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
> I agree, but it should be a simple rewrite. No?
It's NULLs inside the subselect that are the issue.
select 1 in (select a from foo)
select exists ( select 1 from foo where a=1)
If foo.a contains a row with NULL but no rows containing a 1, the abo
On Thu, 10 Jun 2004, Frank van Vugt wrote:
> Could anybody explain why the planner is doing what it is doing?
>
> What could I do to make it easier to choose a better plan?
You might try raising sort_mem to see if it chooses a better plan. I
think it may be guessing that the hash won't fit and
On Tue, 17 Feb 2004, Saleem Burhani Baloch wrote:
> select count(*), sum(vl_ex_stax) , sum(qty) , unit from inv_detail group by unit;
> on both databases.
> PostgreSQL Machine
> **
> P-III 600Mhz (Dell Precision 220)
> 256 MB Ram (RD Ram)
> 40 GB Baracuda Ext2 File System.
> RedHa
On Thu, 13 May 2004, Fabio Panizzutti wrote:
> I don't understand why the planner chose a different query plan on
> identical tables with same indexes .
Because it's more than table structure that affects the choice made by the
planner. In addition the statistics about the values that are there
On Fri, 23 Apr 2004 [EMAIL PROTECTED] wrote:
> I have a query which I think should be using an index all of the time but
> postgres only uses the index part of the time. The index
> (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
> first followed by the selected column
On Wed, 21 Apr 2004, Edoardo Ceccarelli wrote:
>
> > What happens if you go:
> >
> > CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric,
> > LOWER(testo));
> >
> > or even just:
> >
> > CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(LOWER(testo));
> >
> I wasn't able to mak
On Mon, 12 Apr 2004, Jeremy Dunn wrote:
>explain analyze select count(*) from xxx where cid=6223341;
>Aggregate (cost=74384.19..74384.19 rows=1 width=0) (actual
> time=11614.89..11614.89 rows=1 loops=1)
> -> Index Scan using xxx_cid on emailrcpts (cost=0.00..74329.26
> rows=21974
On Mon, 5 Apr 2004, Rosser Schwarz wrote:
> while you weren't looking, Kevin Barnard wrote:
>
> > Have you added indexes for the custid column for tables
> > account.acct accunt.orgacct and note?
>
> They were indexed in the original case, yes. There was no
> need to index them in today's test ca
On Thu, 25 Mar 2004, Qing Zhao wrote:
>select
>_level_ as l,
> nextval('seq_pk_bom_detail') as bom_detail,
> prior nextval('seq_pk_bom_detail') as parent_subassembly,
>parent_part_number,
> customer_part_number,
>/* mfr_name,
> mfr_part,
> description,*/
> c
On Tue, 23 Mar 2004, Stephan Szabo wrote:
> On Tue, 23 Mar 2004, Phil Endecott wrote:
>
> > Dear PostgresQL Experts,
> >
> > I am trying to get to the bottom of some efficiency problems and hope that
> > you can help. The difficulty seems to be with INTERSECT expre
On Tue, 23 Mar 2004, Phil Endecott wrote:
> Dear PostgresQL Experts,
>
> I am trying to get to the bottom of some efficiency problems and hope that
> you can help. The difficulty seems to be with INTERSECT expressions.
>
> I have a query of the form
> select A from T where C1 intersect selec
On Mon, 22 Mar 2004, Joseph Shraibman wrote:
> Tom Lane wrote:
> > Joseph Shraibman <[EMAIL PROTECTED]> writes:
> >
> >>No, pkey is not the primary key in this case. The number of entries in u
> >>that have pkey 260 and not boolfield is 344706.
> >
> >
> > ... and every one of those rows *must* b
On Thu, 18 Mar 2004, Saleh, Amgad H (Amgad) wrote:
>
> Stephan:
>
> In each table we're storing the max. string length.
>
> For example:
>
> for TEST_1, we're storing 'abcdefghjk' and 'lmnop'
> for TEST_2, we're storing 'abcdefghjk' and 'lmnopqrstu'
> for TEST_3, we're storing 'abcdefghjk' and
On Thu, 18 Mar 2004, Saleh, Amgad H (Amgad) wrote:
> Stephan / Stephen
>
> We know about the overhead and do understand the math you've provided.
> This is not the question we're asking. We've just provided the table definitions as
> examples.
>
> The real question was, even with the 52 & 56 (assu
On Wed, 17 Mar 2004, Seum-Lim Gan wrote:
> we have a question about the pagesize in PostgreSQL:
>
> Using different pagesizes: 4K, 8K, 16K, 32K, when we store different
> record sizes
> such as in the following example:
>
> CREATE TABLE TEST_1 (
> F1 VARCHAR(10),
> F2 VARCHAR(5) );
>
> CREATE TABL
On Thu, 11 Mar 2004, Octavio Alvarez wrote:
>
> Hello to everybody.
>
> I ask your help for a severe problem when doing a query that LEFT JOINs
> one table to another ON a field, and then LEFT JOINs again to another
> "instance" of a table ON another field which stores the same entity, but
> with
On Wed, 3 Mar 2004, Chris Kratz wrote:
> Which certainly points to the triggers being the culprit. In reading the
> documentation, it seems like the "delete from only..." statement should
> ignore the constraint triggers. But it seems quite obvious from the
Delete from only merely means that c
On Fri, 20 Feb 2004, Bill Moran wrote:
> I'm converting a SQL application to PostgreSQL. The majority of the logic
> in this application is in the stored functions in the database.
>
> Somewhere, I saw a reference to "WITH (iscachable)" for stored functions,
> looking again, I'm unable to find an
On Wed, 18 Feb 2004, Josh Berkus wrote:
> Stephan,
>
> > Can you give more information? I know that I'm not exactly certain what
> > the situation is from the above and the original query/explain piece.
> >
>
> Believe me, if I posted the query it wouldn't help.Heck, I'd have trouble
> follow
On Wed, 18 Feb 2004, Josh Berkus wrote:
> The planner can't, or doesn't want to, use an index on (event_date,
> (event_date + duration)) where the first column is an ascending sort and the
> second a descending sort.So I've coded a workaround that's quite
> inelegant but does get the correct r
On Sun, 8 Feb 2004, Bruno BAGUETTE wrote:
> > On Fri, 6 Feb 2004, Bruno BAGUETTE wrote:
> >
> > > > In addition to what Tom said, the row estimates look suspiciously
> > > > default. You mention vacuuming, but do you ever analyze
> > > > the tables?
> > >
> > > I run VACUUM FULL ANALYZE with the p
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote:
> > In addition to what Tom said, the row estimates look
> > suspiciously default. You mention vacuuming, but do you ever
> > analyze the tables?
>
> I run VACUUM FULL ANALYZE with the postgres user on all the PostgreSQL
> databases on the server, twice a
On Fri, 6 Feb 2004, Bruno BAGUETTE wrote:
> I was thinking that a index on lower(substr(l_name, 1, 1)) and another
> index on lower(substr(org_name, 1, 1)) should gives better performances.
> When I've to create theses two indexes, it seems like this is not
> allowed :
>
> levure=> CREATE INDEX f
On Fri, 30 Jan 2004, David Teran wrote:
> select
> sum(job_property_difference(t0.int_value, t1.int_value)) as rank
>from
>job_property t0,
>job_property t1
>where
>t0.id_job_profile = 911
>and t0.id_job_attribute = t1.id_job_attribute
>and t1.id_job_profile in (select
On Thu, 29 Jan 2004, Reece Hart wrote:
> I have a large query which I would like to place in a view. The explicit
> query is sufficiently fast, but the same query as a view is much slower
> and uses a different plan. I would appreciate an explanation of why this
> is, and, more importantly whether
On Fri, 23 Jan 2004, Jenny Zhang wrote:
> 3. index with desc/asc is not supported in PG, why it is not needed? Is
> there any work-around?
You can do this with index operator classes. There aren't any
automatically provided ones that do the reversed sort iirc, but I think
that's come up before
On Wed, 21 Jan 2004, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Richard Huxton <[EMAIL PROTECTED]> writes:
>
> > On Tuesday 20 January 2004 16:42, Tom Lane wrote:
> >> Harald Fuchs <[EMAIL PROTECTED]> writes:
> >> > Why? If the underlying table has a primary key, finding correspondin
On Tue, 20 Jan 2004, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Tom Lane <[EMAIL PROTECTED]> writes:
>
> > Richard Huxton <[EMAIL PROTECTED]> writes:
> >> On Tuesday 20 January 2004 00:01, Neil Conway wrote:
> >>> Yeah, I didn't get around to implementing that. If anyone wants this
>
On Sat, 17 Jan 2004, Ceri Storey wrote:
> On Fri, Jan 16, 2004 at 10:17:50AM -0800, Stephan Szabo wrote:
> > As a starting point, we're likely to need the exact query, explain analyze
> > output for the query and version information.
>
> Okay, from top to bottom:
>
On Wed, 14 Jan 2004, Ceri Storey wrote:
> Hi there.
>
> I've got a database (the schema is:
> http://compsoc.man.ac.uk/~cez/2004/01/14/tv-schema.sql) for television
> data. Now, one of the things I want to use this for is a now and next
> display. (much like http://teletext.com/tvplus/nownext.asp
On Wed, 14 Jan 2004, CoL wrote:
[plan1]
> -> Seq Scan on menutable b (cost=0.00..13.01 rows=38 width=22)
> (actual time=0.02..0.38 rows=38 loops=1)
[plan2]
> -> Index Scan using menutable_pkey on menutable b
> (cost=0.00..29.36 rows=38 width=22) (actual time=0.02..0.12 rows=38 loops=1)
On Fri, 9 Jan 2004, Richard Huxton wrote:
> On Friday 09 January 2004 08:57, Dennis Björklund wrote:
> > On Fri, 9 Jan 2004, Richard Huxton wrote:
> > > > > select invheadref, invprodref, sum(units)
> > > > > from invtran
> > > > > group by invheadref, invprodref
> > > >
> > > > For the above quer
1 - 100 of 138 matches
Mail list logo
