Re: [SQL] optimize self-join query

[Harald Fuchs]

In article ,
Ty Busby  writes:

> I have a table that stores a very large starting number called
> epc_start_numeric and a quantity.  I've apparently built the most
> inefficient query possible for doing the job I need: find out if any
> records overlap.  Imagine the epc_start_numeric + quantity
> representing a block of numbers.  I need to find out if any of these
> blocks overlap.

If I understand you correctly, you want to compare numeric intervals.
On PgFoundry you can find an interval type like that called bioseg.
This type is GiST-indexable and thus may speed up your query.

Example:

  CREATE TABLE test2 (
id serial NOT NULL,
seg bioseg NOT NULL,
PRIMARY KEY (id)
  );

  -- Fill test2 with a gazillion of rows

  CREATE INDEX test2_seg_ix ON test2 USING gist (seg);

  SELECT t1.id, t1.seg, t2.id, t2.seg
  FROM test2 t1
  JOIN test2 t2 ON t2.id != t1.id AND t2.seg && t1.seg;

You'll still need a seqscan for t1, but t2 will use an index scan.

You can even define a table constraint to prevent overlaps:

  ALTER TABLE test2
  ADD CONSTRAINT test2_seg_ex
  EXCLUDE USING gist (seg WITH &&);


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Different order by behaviour depending on where clause?

[Phil Couling]

Hi Jan

It is my understanding that a select query without "order by" has an
undefined order.
Though I'm sure you understand the dangers of using something which is
undefined, I'm going reiterate them here:

In general you should never rely on something which is undefined.
Attempting to investigate undefined behavior is a very bad idea
because it can change without warning - especially with databases and
explain plans.

Even when it appears to work (example a and b) you must NEVER rely on it.
This is clearly being affected by the way postgres plans the query.
Since the plan can change depending on the profile of the data, your
application may order things correctly when you build it but suddenly
stop ordering correctly years down the line when it has been filled
with data. Also when postgres gets upgraded no-one guarantees that
undefined behavior will remain unchanged.

As tedious as this is, you're just going to have to order this in your
select queries.

Regards

On 28 October 2011 06:13, Pavel Stehule  wrote:
> Hello
>
> 2011/10/28 Jan Bakuwel :
>> Hi,
>>
>> I have a compound query with some grouping, having and order by's saved
>> as a view, say with name "myview".
>>
>> A) select * from "myview" returns the results as expected in the correct
>> order (the order by is on fields "Category", "Year", "Month" and a few
>> other fields). The results are correctly ordered by these fields as
>> specified in the view.
>>
>> B) select * from "myview" where "Year"=2011 and "Month"=1 also returns
>> the results as expected in the correct order (a subset of A).
>>
>> however
>>
>> C) select * from "myview" where "Year"=2011 and "Month" >= 1 and "Month"
>> <= 1 returns the same resultset as B but the order of the rows is not
>> correct (not even close; haven't been able to see any pattern).
>>
>> Any idea how I can further analyse/diagnose this?
>>
>> regards,
>> Jan
>>
>
> Look on EXPLAIN - these queries will have a different execution plan
>
> http://www.postgresql.org/docs/8.4/static/sql-explain.html
>
> Regards
>
> Pavel Stehule
>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql