Re: [HACKERS] CTE inlining

2017-05-11 Thread Yaroslav
Ilya Shkuratov wrote
> First of all, to such replacement to be valid, the CTE must be 
> 1. non-writable (e.g. be of form: SELECT ...),
> 2. do not use VOLATILE or STABLE functions,
> 3. ... (maybe there must be more restrictions?) 

What about simple things like this?

CREATE OR REPLACE FUNCTION z(numeric) RETURNS boolean AS $$
BEGIN
RETURN $1 <> 0;
END;
$$ LANGUAGE plpgSQL IMMUTABLE COST 1000;

-- This one works:
WITH T AS (
SELECT 1.0 AS v1, 0.0 AS v2
UNION ALL
SELECT 3.0, 1.0
UNION ALL
SELECT 2.0, 0.0
), a AS (
SELECT *
  FROM t
 WHERE z(v2)
)
SELECT *
  FROM a
 WHERE v1/v2 > 1.5;

-- This one gives 'division by zero':
WITH T AS (
SELECT 1.0 AS v1, 0.0 AS v2
UNION ALL
SELECT 3.0, 1.0
UNION ALL
SELECT 2.0, 0.0
)
SELECT *
  FROM (
   SELECT *
 FROM t
WHERE z(v2)
   ) AS a
 WHERE v1/v2 > 1.5;




-
WBR, Yaroslav Schekin.
--
View this message in context: 
http://www.postgresql-archive.org/CTE-inlining-tp5958992p5961086.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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


[HACKERS] calculated fields are not seen in the WHERE clause

2002-10-30 Thread Yaroslav Dmitriev
Hello,

OK
select 1 as ccc where 1=1

ERROR
select 1 as ccc where ccc=1
PostgreSQL said: ERROR: Attribute 'ccc' not found

Is there any way to set conditions on calculated fields values?

Best regards,
Yar



---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



[HACKERS] []performance issues

2002-08-02 Thread Yaroslav Dmitriev

Hello,

Sorry if it's wrong list for the question. Could you suggest some tweaks 
to the PostgreSQL 7.2.1 to handle the following types of tables faster?

Here we have  table stats with  something over one millon records. 
Obvious SELECT COUNT(*) FROM stats  takes over 40 seconds to execute, 
and this amount of time does not shorten considerably in subsequent 
similar requests. All the databases are vacuumed nightly.

CREATE TABLE stats (
   url varchar(50),
   src_port varchar(10),
   ip varchar(16),
   dst_port varchar(10),
   proto varchar(10),
   size int8,
   login varchar(20),
   start_date timestamptz,
   end_date timestamptz,
   aggregated int4
);
CREATE  INDEX aggregated_stats_key ON stats (aggregated);
CREATE  INDEX ip_stats_key ON stats (ip);

stats= explain select count(*) from stats;
NOTICE:  QUERY PLAN:

Aggregate  (cost=113331.10..113331.10 rows=1 width=0)
  -  Seq Scan on stats  (cost=0.00..110085.28 rows=1298328 width=0)

EXPLAIN
stats= select count(*) from stats;
  count  
-
 1298328
(1 row)

The system is FreeBSD-4.6-stable, softupdates on, Athlon XP 1500+, 512 Mb DDR, ATA 100 
HDD.

Thanks in advance,
Yar



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



Re: [HACKERS] []performance issues

2002-08-02 Thread Yaroslav Dmitriev

Christopher Kings-Lynne wrote:

Doing a row count requires a sequential scan in Postgres.

Try creating another summary table that just has one row and one column and
is an integer.
  


I have THREE  summary tables derived from stats with different levels 
of aggregation. They work quite fast,  But:

1) Summary tables grow too
2) There are requests which cannot be predicted, so they involve the 
stats table itself.

So I am still interested in PostgreSQL's ability to deal with 
multimillon records tables.

Best regards,
Yar.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly