CODEWITHSUNDEEP
sqlpostgresqlsql-viewrow-number
user3397199
user3397199

Reputation: 1

row_number() from table or view?

CREATE OR REPLACE VIEW public."masterx" AS 
 SELECT row_number() OVER (ORDER BY st_distance(t1.geom, t2.geom)) AS rownum,
    t1.id_seq,
    t2.pars_id,
    st_distance(t1.geom, t2.geom) AS mesafe
   FROM kadas t2,v7 t1
  WHERE st_dwithin(t2.geom, t1.geom, 400::double precision) = true
  AND t1.datex > ('now'::text::date - '180 days'::interval);

It creates a view. When I use select from table it gives a result but when I select from the above view there is no result. What is the problem?

table t1 is

id_seq|field a | field b| geom|

table 2 is

id| field x  | field y | geom|

I want to join 2 tables with geom columns (both points). __

SELECT row_number() OVER (ORDER BY st_distance(t1.geom, t2.geom)) AS rownum,
    t1.id_seq,
    t2.id,
    st_distance(t1.geom, t2.geom) AS distance
   FROM table2 t2,table1 t1
  WHERE st_dwithin(t2.geom, t1.geom, 400::double precision) = true

If I try to select:

where t2.id= 12345

gives result in 1 second.

But when i use

create view as x (
SELECT row_number() OVER (ORDER BY st_distance(t1.geom, t2.geom)) AS rownum,
        t1.id_seq,
        t2.id,
        st_distance(t1.geom, t2.geom) AS distance
       FROM table2 t2,table1 t1
      WHERE st_dwithin(t2.geom, t1.geom, 400::double precision) = true 
)

hen try to query on this view there is no result. What is the problem?

it seems rownumber is problem here but why

__

table 1 v7 has 2 milyon row.
table 2 kadas t2 has 1 milyon row. 

select * from masterx.  
where  pars_id =422328447

where is no solution.
what i want to do is: select points in t1 that is in a buffer with t2 points in 400 meters and order them by distance. there is no problem to here but when I try to take row numbers by the distance for limiting the query for 100 first rows it makes db and comp mad and there is no solution**

Upvotes: 0

Views: 1238

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

Not sure, but I would simplify the CREATE statement like this:

CREATE OR REPLACE VIEW public.masterx AS 
SELECT row_number() OVER (ORDER BY st_distance(t1.geom, t2.geom)) AS rownum,
       t1.id_seq,
       t2.pars_id,
       st_distance(t1.geom, t2.geom) AS mesafe
FROM   v7    t1
JOIN   kadas t2 ON st_dwithin(t1.geom, t2.geom, 400::float8)
WHERE  t1.datex > (now()::date - 180)
ORDER  BY 1;

'now' is a constant, I assume you want the function now() instead. Per documentation:

Tip: You do not want to use the third form ('now') when specifying a DEFAULT clause while creating a table. The system will convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed, the time of the table creation would be used!

You can subtract integer from date.
And you shouldn't rely on the sort order derived from the window function. Add an explicit ORDER BY.

Call:

SELECT * FROM public.masterx

Performance

You have 1 million x 2 million rows. That's 2,000,000,000,000 possible combinations. Your condition t1.datex > (now()::date - 180) reduces one side somewhat, but a huge number will remain. Appropriate indexes on the geom columns help a lot. That can explain why, with the condition

where t2.id = 12345

.. it only takes 1 second. You have reduced the operation by a factor of 1 million. Without that condition it takes ~ 1 million times as long ...

Upvotes: 1

Related Questions