Stephan Szabo <[EMAIL PROTECTED]> writes:
> 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 o
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
[EMAIL PROTECTED]> create view v1 as
select distinct on (AH.p2gblataln_id) AH.p2gblataln_id,H.pseq_id,min(H.pstart) as
"pstart",
max(H.pstop) as "pstop",A.ident,(A.ident/Q.len::float*100)::int as "pct_ident",
sum(H.pstop-H.pstart+1) as "aln_length",H.genasm_id,H.chr,H.plus_strand,min(H.gstart) as
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/how I might coax the view to use a differen
On Thu, 2004-01-29 at 14:01, Tom Lane wrote:
> Probably better to repost it as a gzip'd attachment. That should
> protect the formatting and get it into the list archives.
>
> regards, tom lane
complete with a picture of the GUI version. 26k zipped, let's see if
this makes
Richard Huxton <[EMAIL PROTECTED]> writes:
>> Index Scan using testtab_name_date_from on testtab (cost=0.00..2.01
>> rows=1 width=18)
>> Index Cond: ((name)::text = 'name1'::text)
>> Filter: ((date_from)::timestamp with time zone =
>> ('now'::text)::timestamp(6)with time zone)
> What types are th
Jack Coates <[EMAIL PROTECTED]> writes:
> yup -- here it is. It will probably be a nasty mess after linewrap gets
> done with it,
yup, sure is :-( If I was familiar with the layout I could probably
decipher where the line breaks are supposed to be, but right now I'm
just confused.
> so let me kn
On Thursday 29 January 2004 19:29, [EMAIL PROTECTED] wrote:
> I have 2 columns index.
> The question is if optimizer can use both columns of an index or not,
Should do.
> i.e. the plan should read like this:
>
> Index Cond:
> ((name)::text = 'name1'::text)
> AND ((date_from)::ti
On Thu, 2004-01-29 at 11:31, Tom Lane wrote:
> Jack Coates <[EMAIL PROTECTED]> writes:
> > jackdb=# explain SELECT DISTINCT members_.memberid_
> > jackdb-# FROM members_
> > jackdb-# WHERE ( members_.List_='list1'
> > jackdb(# AND members_.MemberType_='normal'
> > jackdb(# AND members_.SubType_
Jack Coates <[EMAIL PROTECTED]> writes:
> jackdb=# explain SELECT DISTINCT members_.memberid_
> jackdb-# FROM members_
> jackdb-# WHERE ( members_.List_='list1'
> jackdb(# AND members_.MemberType_='normal'
> jackdb(# AND members_.SubType_='mail'
> jackdb(# AND members_.emailaddr_ IS NOT NULL )
I have 2 columns index.
The question is if optimizer can use both columns of an index or not,
i.e. the plan should read like this:
Index Cond:
((name)::text = 'name1'::text)
AND ((date_from)::timestamp with time zone=
('now'::text)::timestamp(6) with time zone)
Whilst
Josh Berkus wrote:
Shridhar, Bill,
Is MSSQL allows to mix rows of two types in single function invocation,
I am sure that would be a hell lot of porting trouble..
There's also the question of whether or not PG would every want to do this.
Frankly, as a once-upon-a-time SQL Server application dev
On Thu, 2004-01-29 at 10:05, Tom Lane wrote:
> Jack Coates <[EMAIL PROTECTED]> writes:
> > That completed in 3.5 minutes on MS-SQL. I killed the query this morning
> > after 15 hours on PostgreSQL 7.4. I tried a GROUP BY memberid_ HAVING
> > variation, which completed in 59 seconds on MS-SQL. I kil
Shridhar, Bill,
> > Is MSSQL allows to mix rows of two types in single function invocation,
> > I am sure that would be a hell lot of porting trouble..
There's also the question of whether or not PG would every want to do this.
Frankly, as a once-upon-a-time SQL Server application developer, I
Jack Coates <[EMAIL PROTECTED]> writes:
> That completed in 3.5 minutes on MS-SQL. I killed the query this morning
> after 15 hours on PostgreSQL 7.4. I tried a GROUP BY memberid_ HAVING
> variation, which completed in 59 seconds on MS-SQL. I killed it after 35
> minutes on PostgreSQL.
Hm. I'd li
On Wed, 2004-01-28 at 18:04, Tom Lane wrote:
> Jack Coates <[EMAIL PROTECTED]> writes:
> > I've got a query that needs some help, please. Is there a way to avoid
> > all the looping? I've got freedom to work with the double-indented
> > sections below ) AND (, but the initial select distinct wrappe
One other suggestion I forgot is that this should move over to the
performance list rather than being on the sql list. The right people
are more likely to see your question there.
On Thu, Jan 29, 2004 at 16:02:06 +0100,
Alexandra Birch <[EMAIL PROTECTED]> wrote:
>
> > >
> > > Postgres choses th
17 matches
Mail list logo
