I see that attribute project is defined as integer in library, and as varchar(8) in clone. I suspect that's what causing the problem and forcing a seq scan on library.
On Thu, 2004-03-04 at 14:56, Charles Hauser wrote:
All,
I have the following query which is running quite slow on our server
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Thursday 04 March 2004 9:56 am, Charles Hauser wrote:
> All,
>
> I have the following query which is running quite slow on our server
> and was hoping someone would have suggestions how I might improve it.
>
Might want to try emailing the performan
Charles Hauser <[EMAIL PROTECTED]> writes:
> I have the following query which is running quite slow on our server and
> was hoping someone would have suggestions how I might improve it.
Have you vacuumed or analyzed these tables recently? The EXPLAIN
numbers show that the planner thinks all the t
All,
I have the following query which is running quite slow on our server and
was hoping someone would have suggestions how I might improve it.
est3=>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.clone_id
est3-> FROM library,clone_aceg
est3-> JOIN clone USING (clone_id)
e
On Thu, 07 Nov 2002 09:57:27 +0100
Christoph Haller <[EMAIL PROTECTED]> wrote:
> > ORDER BY proj.project_id ;
>
> What about simply replacing ORDER BY proj.project_id ; by
> GROUP BY project_id, marketing_name ;
You're right. Thanks a lot.
Regards,
Masaru Sugawara
>
> This is the final query, can anyone see anything wrong with it?:
> SELECT projects.project_id, projects.marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-08'}
> THEN lots.lot_id ELSE NULL END
>
e-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Christoph Haller
> Sent: Thursday, November 07, 2002 3:57 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] query optimization question
>
>
> > SELECT
> > project_
60 >=60def total
X 1 2 1 4 5 10 5 20 (if X had 4
lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3
deficiencies in project Y)
Terry Fielder
Network Engineer
Great Gulf H
> SELECT
> project_id,
> marketing_name,
> COUNT(lots.lot_id) AS def_count,
> COUNT(CASE WHEN dt.days_old_start_date < {d '2002-10-07'}
> THEN lots.lot_id ELSE NULL END) AS
def_count_less_30,
> COUNT(CASE WHEN dt.days_old_start_date >= {d '2002-10-07'}
Wednesday, November 06, 2002 11:44 AM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] query optimization question
>
>
> On Wed, 6 Nov 2002 09:01:49 -0500
> <[EMAIL PROTECTED]> wrote:
>
> > If anyone can see a way to do a group by to do this
On Thu, 07 Nov 2002 01:44:25 +0900
I wrote <[EMAIL PROTECTED]> wrote:
> On Wed, 6 Nov 2002 09:01:49 -0500
> <[EMAIL PROTECTED]> wrote:
>
> > If anyone can see a way to do a group by to do this, then I will be happy to
> > hear about it, because currently the resultset has to do a separate
> > (s
On Wed, 6 Nov 2002 [EMAIL PROTECTED] wrote:
> Actually, come to think of it, just the implementation of re-querying a
> temporary table could alone significantly improve performance, because the
> temp table would:
> a) have fewer records to scan on the subselects
> b) not require any joins
Yeah,
On Wed, 6 Nov 2002 09:01:49 -0500
<[EMAIL PROTECTED]> wrote:
> If anyone can see a way to do a group by to do this, then I will be happy to
> hear about it, because currently the resultset has to do a separate
> (sequential or index) scan of the deficiencies table. The only way I can
> see to do
/ Ashton Woods Homes
[EMAIL PROTECTED]
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:pgsql-sql-owner@;postgresql.org]On Behalf Of Stephan Szabo
> Sent: Wednesday, November 06, 2002 11:22 AM
> To: [EMAIL PROTECTED]
> Cc: Postgresql Sql Group (E-mail)
> Subject
On Wed, 6 Nov 2002 [EMAIL PROTECTED] wrote:
> However, for the total deficiencies I am then splitting up the total into
> aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
> looks like the below. But before I paste it in, I would like to optimize
> it, if I could do so wit
Now that I've given your problem more thoughts (and searched for similar
stuff),
I think what you need is generating a cross table resp. pivot table.
Related to this, I am thinking of a query using Conditional Expressions
like
COUNT ( CASE WHEN ... THEN 1 ELSE NULL) in order to use GROUP BY.
Toget
On Wednesday 06 Nov 2002 2:01 pm, [EMAIL PROTECTED] wrote:
> However, for the total deficiencies I am then splitting up the total into
> aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
> looks like the below. But before I paste it in, I would like to optimize
> it, if I
60 >=60def total
X 1 2 1 4 5 10 5 20 (if X had 4
lots, each of 5 deficiencies)
Y 1 1 0 2 3 3 0 6 (each has eg 3
deficiencies in project Y)
Terry Fielder
Network Engineer
Great Gulf H
The query below is slow because both the lots table and the deficiency_table
table have thousands of records. Can anyone tell me how to do the second
subselect (lot_count) by some method of a join instead of a sub - subselect
OR any other method I can use to optimize this query to make it faster?
19 matches
Mail list logo
