Re: [HACKERS] pg_xlogdump --stats

[gotoschool6g]

 
 I'm pretty sure that most committers would want to apply the 
 rm_identity part in a separate commit first. Could you make it 
 two patches, one introducing rm_identity, and another with 
 xlogdump using it? 

Carp the code:

const char *
clog_identify(uint8 info)
{ 
 switch (info)
 {
  case CLOG_ZEROPAGE:
   return ZEROPAGE;
  case CLOG_TRUNCATE:
   return TRUNCATE;
   break;
 }
 return NULL;
}

or

const char *
clog_identify(uint8 info)
{
if(info==CLOG_ZEROPAGE)return ZEROPAGE;
if(info==CLOG_TRUNCATE)return TRUNCATE;
return NULL;
}

is a bit faster than:

const char *
clog_identify(uint8 info)
{
 const char *id = NULL;

 switch (info)
 {
  case CLOG_ZEROPAGE:
   id = ZEROPAGE;
   break;
  case CLOG_TRUNCATE:
   id = TRUNCATE;
   break;
 }

 return id;
}

Re: [HACKERS] Can simplify 'limit 1' with slow function?

[gotoschool6g]

slow query(8531 ms):
SELECT ST_Distance_Sphere(shape,ST_GeomFromText('POINT(116.41386186784513 
40.12211338311868)')) FROM road order by id LIMIT 1;

explain output:
Limit  (cost=4653.48..4653.48 rows=1 width=3612)
  -  Sort  (cost=4653.48..4683.06 rows=11832 width=3612)
Sort Key: id
-  Seq Scan on road  (cost=0.00..4594.32 rows=11832 width=3612)

fast query(16ms):
select ST_Distance_Sphere(shape,ST_GeomFromText('POINT(116.41386186784513 
40.12211338311868)')) from (SELECT shape FROM road order by id  LIMIT 1) a

explain output:
Subquery Scan on a  (cost=1695.48..1695.74 rows=1 width=3608)
  -  Limit  (cost=1695.48..1695.48 rows=1 width=3612)
-  Sort  (cost=1695.48..1725.06 rows=11832 width=3612)
  Sort Key: road.id
  -  Seq Scan on road  (cost=0.00..1636.32 rows=11832 width=3612)

CREATE TABLE road
(
  shape geometry,
  id integer
)
WITH (
  OIDS=FALSE
);

There are redundant call when sorting?


 On Tue, Jul 1, 2014 at 2:16 PM, Martijn van Oosterhout 
 klep...@svana.org wrote: 
  On Sun, Jun 29, 2014 at 10:05:50PM +0800, gotoschool6g wrote: 
  The simplified scene: 
  select slowfunction(s) from a order by b limit 1; 
  is slow than 
  select slowfunction(s) from (select s from a order by b limit 1) as z; 
  if there are many records in table 'a'. 
  
  
  The real scene. Function  ST_Distance_Sphere is slow, the query: 
  SELECT ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from road 
  order by c limit 1; 
  is slow than: 
  select ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from (SELECT s 
  from road order by c limit 1) as a; 
  There are about 7000 records in 'road'. 
  
  I think to help here I think we need the EXPLAIN ANALYSE output for 
  both queries. 
  
 Well, I think the problem is a well understood one: there is no 
 guarantee that functions-in-select-list are called exactly once per 
 output row.  This is documented -- for example see here: 
 http://www.postgresql.org/docs/9.1/static/explicit-locking.html#ADVISORY-LOCKS.
  
 In short, if you want very precise control of function evaluation use 
 a subquery, or, if you're really paranoid, a CTE. 

I'm probably dense, but I'm not sure I understand. Or it is that the 
slowfunction() is called prior to the sort? That seems insane. 

Have a nice day, 
--  
Martijn van Oosterhout   klep...@svana.org   http://svana.org/kleptog/ 
 He who writes carelessly confesses thereby at the very outset that he does 
 not attach much importance to his own thoughts. 
   -- Arthur Schopenhauer 
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Can simplify 'limit 1' with slow function?

[gotoschool6g]

The simplified scene: 
select slowfunction(s) from a order by b limit 1;
is slow than
select slowfunction(s) from (select s from a order by b limit 1) as z;
if there are many records in table 'a'.


The real scene. Function  ST_Distance_Sphere is slow, the query:
SELECT ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from road order by 
c limit 1;
is slow than:
select ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from (SELECT s from 
road order by c limit 1) as a;
There are about 7000 records in 'road'.  

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