> On 21 Feb 2015, at 12:03, Arup Rakshit wrote:
>
> On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:
>>
>>> On 21 Feb 2015, at 9:34, Arup Rakshit wrote:
>>>
>>> Select * from Emp
>>> where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 =
>>> val14and attr2 = v
On Saturday, February 21, 2015 12:42:03 PM Alban Hertroys wrote:
>
> > On 21 Feb 2015, at 9:34, Arup Rakshit wrote:
> >
> > Select * from Emp
> > where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 =
> > val14and attr2 = val15 and attr3 = val16);
> >
> > Now suppose I got (x1,
> On 21 Feb 2015, at 9:34, Arup Rakshit wrote:
>
> Select * from Emp
> where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 =
> val14and attr2 = val15 and attr3 = val16);
>
> Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my
> query as :
>
> Select *
Hi,
Suppose I want to select the rows from a table, say Emp, as below :
Select * from Emp
where (attr1 = val11 and attr2 = val12 and attr3 = val13) or (attr1 = val14and
attr2 = val15 and attr3 = val16);
Now suppose I got (x1, x2, x3) and (y1, y2, y3). Then I need to rewrite my
query as :
Sel
On Wed, Oct 29, 2014 at 8:47 PM, David Johnston
wrote:
> On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo > wrote:
>
>>
>> SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
>> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
>> field10, field11, field12, fiel
On Wed, Oct 29, 2014 at 12:14 PM, Jorge Arevalo
wrote:
>
> SELECT value1,value2,value3,value4, value5, hstore(ARRAY['field9',
> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> field10, field11, field12, field13, field14]) as metadata, value7, (select
> array((select row(f1
Jorge Arevalo writes:
>> On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane wrote:
>>> I'm also a bit confused as to why the planner is saying that the (SELECT
>>> ARRAY(...)) bit is an InitPlan and not a SubPlan. That implies that
>>> "field7" in the innermost WHERE clause is not a reference to table1
On Wed, Oct 29, 2014 at 8:05 PM, David Johnston
wrote:
> On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane wrote:
>
>> Jorge Arevalo writes:
>>
>> > This is the result of EXPLAIN ANALYZE
>>
>> >QUERY
>> > PLAN
>> >
>>
On Wed, Oct 29, 2014 at 11:53 AM, Tom Lane wrote:
> Jorge Arevalo writes:
>
> > This is the result of EXPLAIN ANALYZE
>
> >QUERY
> > PLAN
> >
>
Jorge Arevalo writes:
> SELECT value1,value2,value3,value4,
> value5, hstore(ARRAY['field9', 'field10', 'field11', 'field12', 'field13',
> 'field14'], ARRAY[field9, field10, field11, field12, field13, field14) as
> metadata, value7, (select array((select row(f1, f2) from table2 p where
> p.f3 = fi
List preference is to inline post or, at worse, bottom post. Please do not
top post.
On Wed, Oct 29, 2014 at 11:06 AM, Jorge Arevalo
wrote:
> Hello David, many thanks for your responses,
>
> Sorry for not providing the content of the fill_table3_function, but it
> just executes 3 insert queries
Hello David, many thanks for your responses,
Sorry for not providing the content of the fill_table3_function, but it
just executes 3 insert queries in 3 different tables. And I've checked the
time consuming operation is in this query (by the way, there was a little
mistake in the name of the field
Jorge Arévalo-2 wrote
> (SELECT value1,value2,value3,value4,value5, hstore(ARRAY['field9',
> 'field10', 'field11', 'field12', 'field13', 'field14'], ARRAY[field9,
> field10, field11, field12, field13, field14) as metadata, value7, (select
> array((select row(f1, f2) from table2 p where p.field7 = f
Jorge Arévalo-2 wrote
> So, I first generate a common table records_to_insert, using data from
> table1 and table2, and then call a function fill_table3_function, in order
> to insert the values into table3 (I do more things apart from insert,
> that's reason to call a function instead of just rais
Hello,
I'm trying to optimize a query that takes too much time. This is what I have
table1(field1, field2... field14): contains about 8.5 million rows
table2(f1, f2, f3): contains about 2.5 million rows
table3: is empty, and must be filled with data coming from table1 and table2
To fill table3,
Hi,
On Wed, Apr 11, 2012 at 2:28 PM, wrote:
> I'm looking for materials about query optimization in PostgreSQL. I read
> documentation but it doesnt contain much information. Can you recommend a
> good book or website when i could learn something about this?
I think you can find some good in
Hi,
I'm looking for materials about query optimization in PostgreSQL. I read
documentation but it doesnt contain much information. Can you recommend a good
book or website when i could learn something about this?
thanks in advance for your help
--
Sent via pgsql-general mailing list (p
ot;Index Cond: ($0 = securitytypekey)"
So this proves it is using the index.
But I think the issue is in the interaction between the numbers of rows
returned from positions_gsco and security.
kd
-Original Message-
From: Klint Gore [mailto:[EMAIL PROTECTED]
Sent: Thurs
Kevin Duffy wrote:
So here are the questions for the PSQL gurus:
Is getsectypekey(‘CFD’) executing for every join (or possible join)
between positions_gsco and security?
Causing a scan of security for every possible join.
Does ‘ (select getsectypekey('CFD') ) ‘ cause the getsectype()
funct
What does the output of explain select sec.* ... have to say?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello:
This posting is a follow up to this posting from July 15th.
http://archives.postgresql.org/pgsql-general/2008-07/msg00569.php
Given the following select statement
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
u
Hello:
I need your kind assistance to debug an optimization issue.
The following two select statements in my book are almost identical.
One does a lookup for security type 'CFD' and
the other does the same lookup except for security 'OP'. When run with
'CFD' the query never returns.
Wh
Orest Kozyar wrote:
What I am wondering is whether the database first eliminate all rows in
tableA that don't meet the criteria before performing the join, or does it
perform the join first then eliminate all records that don't meet the
criteria?
If you use "EXPLAIN SELECT ..." then PostgreSQL
I have the following sql:
CREATE OR REPLACE FUNCTION foo (in x integer) RETURNS float AS $$
SELECT max(tableB.columnC)
FROM
tableA inner join tableB on (tableA.columnA =
tableB.columnB)
WHERE
tableA.columbA = x
... (additional code to select
> --- Original Message ---
> From: Peter <[EMAIL PROTECTED]>
> To: pgsql-general@postgresql.org
> Sent: 03/02/07, 10:58:08
> Subject: Re: [GENERAL] Query optimization problem
>
> >>> Try SELECT count(*) FROM ... instead and see if the times are clos
Try SELECT count(*) FROM ... instead and see if the times are closer.
Correct. That executes in 300ms flat:
So your problem is data transfer, not the query per se.
Well, based on PgAdmin times I suspected some sort of heavy 'data
preparation before it's sent out' overhead
How old a pgAdm
Peter <[EMAIL PROTECTED]> writes:
>> Try SELECT count(*) FROM ... instead and see if the times are closer.
> Correct. That executes in 300ms flat:
So your problem is data transfer, not the query per se.
How old a pgAdmin are you using?
regards, tom lane
>> I'm not sure if I'm heading up the right alley - seems too simple!,
but here is my issue.
>>
>> I have about 3000 records in 'mytable', and simple
>
>Number of rows is not the most important thing here - the number of
>occupied disc pages is (you can have a lot of small rows or a small
>nub
Query returned successfully with no result in 600 ms.
Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?
Data transfer and display time usually
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/02/07 07:22, Richard Huxton wrote:
> Peter wrote:
>>
>> Query returned successfully with no result in 600 ms.
>>
>> Why SELECT takes 3+ second to execute? Is it something to do with my
>> Postgres server optimization, or PgAdmin does not show cor
Richard Huxton wrote:
Peter wrote:
Query returned successfully with no result in 600 ms.
Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?
Peter wrote:
Query returned successfully with no result in 600 ms.
Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?
Data transfer and displ
I'm not sure if I'm heading up the right alley - seems too simple!, but
here is my issue.
I have about 3000 records in 'mytable', and simple
select * from mytable where x=1
is timed as:
Total query runtime: 2933 ms.
Data retrieval runtime: 791 ms.
EXPLAIN says it's Seq Scan, but the actual f
Hello Everybody,
I have a question about optimization of queries which includes UDFs.
Does anybody know what the Postgres does for optimizing the queries with UDFs?
Does the Postgres query optimizer do anything special with UDFs?
Thanks,
Jungmin Shin
Gregory Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> only the a = 5 clause would be used with the index. As of 8.1 it will
>> consider using nonconsecutive index columns
> Really? Is this the "skip scan" plan people were pining for?
No, there's no skip scan, it jus
Tom Lane <[EMAIL PROTECTED]> writes:
> Before (if memory serves) 8.1, the planner would only consider leading
> index columns as potential indexscan qualifiers. So given
>
> where a = 5 and c = 4;
>
> only the a = 5 clause would be used with the index. As of 8.1 it will
> consider using
[EMAIL PROTECTED] writes:
> Suppose I have an index on 5 columns (A, B, C, D, E).
> If my WHERE clause is not in that order, will the optimizer reorder
> them as necessary and possible?
Yes, the optimizer understands about commutativity/associativity of
AND and OR ;-)
> If I don't specify co
Suppose I have an index on 5 columns (A, B, C, D, E).
If my WHERE clause is not in that order, will the optimizer reorder
them as necessary and possible?
WHERE A=1 AND C=3 AND B=2 AND E=5 AND D=4
Obviously it can't reorder them in all cases:
WHERE A=1 AND (C=3 OR B=2) AND (E=5 OR D
On Fri, Sep 30, 2005 at 06:24:52PM -0400, Sean Davis wrote:
>-> Seq Scan on u_all_est_mrna c (cost=0.00..932582.74 rows=24225174
> width=179) (actual time=17.384..302484.904 rows=24225174 loops=1)
That step is where most of your time is being spent, which isn't
surprising given that it's sca
I have a couple of very large tables that I am querying on that gives the
following explain analyze output. If someone can help out with my mess,
that would be great.
Thanks,
Sean
explain analyze
select e.*,c.*
from u_all_est_mrna c
join g_rna_acc d on c.accession=d.accession,
hi guys , Richad
you are right!, my apologies
profile data:
postgres: "PostgreSQL 7.4.3 on i386-pc-linux-gnu,
compiled by GCC 2.95.4"
HP proliant
processor: Dual Xeon 3.40 Ghz.
HD:250 Gigabytes hard disk storage.
responses:
> Comment 4 - you apply a LIMIT without an ORDER BY.
> Are
marcelo Cortez wrote:
hi guys
i need advice for query optimization,take too long
time.
any coments are welcomed :).
Comment 1 - you'll need to post the output of "EXPLAIN ANALYSE", not
just EXPLAIN, otherwise people can't see how long it took.
Comment 2 - you provide the definition of o
hi guys
i need advice for query optimization,take too long
time.
any coments are welcomed :).
best
MDC
SELECT c.actuacion_car AS c_actuacion,
c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
Peter Eisentraut wrote:
Miroslav ¦ulc wrote:
is there on the net any general howto on SQL query optimizations? We
have recently moved our project from MySQL to PostgreSQL and are
having problem with one of our queries.
I doubt that there is a generic documentation on SQL optimization,
beca
Bruce Momjian wrote:
Have you read the FAQ?
Yes, but I have found only some useful information saying when indexes
are not used.
Miroslav Šulc
begin:vcard
fn;quoted-printable:Miroslav =C5=A0ulc
n;quoted-printable:=C5=A0ulc;Miroslav
org:StartNet s.r.o.
adr;quoted-printable;quoted-printable:;;Vrchl
On Sun, 2005-03-13 at 02:34 +0100, Miroslav Šulc wrote:
> is there on the net any general howto on SQL query optimizations? We
> have recently moved our project from MySQL to PostgreSQL and are having
> problem with one of our queries. The EXPLAIN command is surely useful
> but I don't know how
Miroslav ¦ulc wrote:
> is there on the net any general howto on SQL query optimizations? We
> have recently moved our project from MySQL to PostgreSQL and are
> having problem with one of our queries.
I doubt that there is a generic documentation on SQL optimization,
because this heavily depends
Hi all,
is there on the net any general howto on SQL query optimizations? We
have recently moved our project from MySQL to PostgreSQL and are having
problem with one of our queries. The EXPLAIN command is surely useful
but I don't know how to read it and how to use the output to optimize
the qu
Jeff Davis <[EMAIL PROTECTED]> writes:
> I had an idea about using aggregates: what if I made an aggregate function
> called "first" that just returned the value in the first tuple it
> encountered?
You could make that work in 7.4, but not in any existing releases.
The trouble is that you need
49 matches
Mail list logo
