Rebecca Clarke | 23 Jul 11:45 2014
Picon

Index usage with slow query

Hi all,

Looking for some advice regarding a slow query I have and indexing. 

I'm using postgresql 9.1 and this is my table that has around 6800000 rows:

CREATE TABLE mytable
(
  class character varying,
  floor character varying,
  source_id integer,
  the_geom geometry
)
WITH (
  OIDS=TRUE
);


INDEX idx_source_id
    USING btree
  (source_id);

INDEX idx_the_geom_gist
  USING gist
  (the_geom);


This table is constantly hit with the below query (not always the same values in the where). The only difference between queries are the values in the where clause:

SELECT the_geom,oid from mytable
WHERE
the_geom && ST_GeomFromText('POLYGON((529342.334095833 180696.221733333,529342.334095833 181533.44595,530964.336820833 181533.44595,530964.336820833 180696.221733333,529342.334095833 180696.221733333))',find_srid('','mytable','the_geom')) 
AND 
(floor = 'gf' AND source_id = '689' AND class = 'General')


As the table has increased in size, this query has become slower, so I made this index:


INDEX idx_floor_sourceid_class
  USING btree
  (floor, source_id, class);


When I run an EXPLAIN and EXPLAIN ANALYZE the query isn't using this new index. 

Sometimes it uses just idx_the_geom_gist

other times it uses idx_the_geom_gist and idx_source_id


I don't understand why it's inconsistent in its use of indexes when the query is always the same structure, just different where clause values, and I don't understand why it's not using the new index either.


Would love some help with this. I'm not sure where I'm going wrong.

Thanks in advance.





Kalai R | 23 Jul 08:14 2014
Picon

(unknown)

Well the above would indicate it is not being shutdown properly.

How are you shutting it down?

PSQL server starterd as stoped by postgresql windows service. So when I shutdown the machine postgres shutting down by postgres windows service

What and how are you installing?

Using windows binary zip file, I extract zip file and create cluster using intidb then register windows service using pg_ctl

Who are you running the service as?

I try to start the psql server from command prompt using pg_ctl.exe
Kalai R | 23 Jul 06:01 2014
Picon

PSQL Server couldn't start

Hi,

I am facing a serious problem with postgresql frequently. I am using postgresql 9.3 in Windows OS with VisualStudio. we have more customers. 

We shutting down the system properly. But when we booting system, postgresql service  didn't start. This happens frequently after we install.

log details when I trying to start the psql server

server starting
LOG:  database system was interrupted while in recovery at 2014-07-22 18:31:34 IST
HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/1772908
FATAL:  could not remove symbolic link "pg_tblspc/17681": Permission denied
CONTEXT:  xlog redo create tablespace: 17681 "C:/GLOIER/gPRO/Data/GT"
LOG:  startup process (PID 4992) exited with exit code 1
LOG:  aborting startup due to startup process failure


Why this happens?

Help me to solve this problem.

Thank You.


Ramesh T | 22 Jul 16:21 2014
Picon

Fwd: Need r_constraint_name



---------- Forwarded message ----------
From: Ramesh T <rameshparnanditech <at> gmail.com>
Date: Tue, Jul 22, 2014 at 7:50 PM
Subject: Re: [GENERAL] Need r_constraint_name
To: Adrian Klaver <adrian.klaver <at> aklaver.com>


Just i'm retriving the constraint_name when  i enter child_table_name for inner query and that constraint name is checking 
parent_table on outer statement that constraint_name is equal then display the constraint name ..?but outer select is r_constraint_name

i think in postgres r_constraint_name is also include in the pg_constraints details not a seperate column in postgres for that ,if parent table have consraint_name same as the child table return from inner query that constraint_name displayed out..

 my assumption..is it corect?
from last query..


thanks in advance..
ramesh


On Tue, Jul 22, 2014 at 7:18 PM, Adrian Klaver <adrian.klaver <at> aklaver.com> wrote:
On 07/22/2014 03:12 AM, Ramesh T wrote:
thank u ,

SELECT constraint_name
                 FROM information_schema.table_constraints AS tc
        WHERE     tc.table_name = p_table_name
              AND constraint_name IN (SELECT constraint_name
                                        FROM
information_schema.table_constraints AS tc
                                       WHERE     tc.table_name =
                                                    p_ref_table_name
                                             AND tc.constraint_type =
                                                    'PRIMARY KEY');

is this correct process same as above ..

but i want check "r_constraint_name" instead of "constraint_name"  in
outer statement in above code..

I am not sure you are going to find that column. I am not an Oracle user but I did find this:

http://docs.oracle.com/html/B13531_01/ap_d.htm

R_CONSTRAINT_NAME is the name of the unique constraint definition for the referenced table.

So it would seem r_constraint_name is an column name in an Oracle system view. I know of no such name in the Postgres system catalog. I am sure the same information is available, you are just going to have to be specific about what you are looking for. From the above that would seem to be the name of the unique key that a foreign key references.

Is that correct?

If so the query you show above will not work as a UNIQUE key does not necessarily have to be the PRIMARY KEY.



please let me know..

thanks in advance,
ramesh


--
Adrian Klaver
adrian.klaver <at> aklaver.com


klo uo | 23 Jul 00:16 2014
Picon

question about memory usage

Hi,

I run Windows and I started using 64 bit PostgreSQL 9.3 a month ago.
I have several PostGIS databases on localhost, with these statistics:

===============================================================
                     Xact
         Xact        Rolled  Blocks  Blocks   Tuples    Tuples
Size     Committed   Back    Read    Hit      Returned  Fetched
---------------------------------------------------------------
1839 MB      52290    105      7593  1962771  27426946   502140
 312 MB         23      0       177     1671      2796      902
 340 MB         31      0       242     2867      4138     1426
  69 MB      40796     21      7996  2019122  27852048   463184
  71 MB         23      0       176     1674      2101      914
 663 MB         37      0       328     2825     14240     1481
6298 kB      51680      0       950  1431658  23355239   291505
===============================================================


Looking in process explorer, I see unusual size for postgres server process, i.e. working set reported around 1GB: http://i.imgur.com/HmkvFLM.png (same in attachment)

I also use SqlExpress server with several databases (including spatial) but that doesn't go above 100MB in Private Bytes and around 1 MB in Working Set.

I wanted to ask, if this is normal, or there is some problem with my server setting?


Thanks

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Martin Gudmundsson | 22 Jul 13:10 2014
Picon

hstore/jsonb support in hibernate/JPA

Hi all!
I saw that Rails 4 comes with hstore support out of the box.

Does anyone know if there’s any integrated support like that in hibernate or any other JPA implementation?
I know your able to write your own custom datatypes, but I’m looking for integrated standardized support
in the base library.

 Kind regards, Martin

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rebecca Clarke | 22 Jul 11:36 2014
Picon

What query currently running within function

Hi all,

Presently I'm executing a function that runs many queries within it.

select * from _myfunction();

Is there a way to see what query it is up to within the function? 
When I do a select of pg_stat_activity it just shows me the _myfunction() query.

I'm running postgresql 9.1

Thanks in advance.
Larry White | 22 Jul 04:06 2014
Picon

inequality testing in jsonb query

Hi, 

Is it possible to query a table with a jsob column to find values that were in some range? For example, If I have a document like this (from the PG documentation:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

Could I modify the following query to find those records where the date "registered" is between November 1, 2009 and November 30, 2009? 

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc <at> > '{"company": "Magnafone"}';


Thank you for your help.

larry

lucas.gary@gmail.com | 21 Jul 23:21 2014
Picon

Question regarding log files in postgresql



I've rolled postgres to 'production', so box is in prod, but the applications aren't active yet.

When I rolled the new box I'm seeing 2 log files:

postgresql-yyyy-MM-dd_hhmmss.log 
AND
postgresql-9.3-main.log

The 'main' log doesn't appear to be used, however the time stamped file is.

Does this make sense?  Is it normal?  

My logrotate entry makes some assumptions that may be incorrect now.

Gary
Karthik Iyer | 21 Jul 17:35 2014

Upgrade to 9.3

Hello,

     We are planning to upgrade Postgres from 9.0 to 9.3. Was wondering 
if there are any serious changes that I have to look out for 
(syntax/datatypes changes) so that my code does not break.

Thanks in advance.

- Karthik

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sergiy Zuban | 18 Jul 20:57 2014
Picon

postgres_fdw - push down conditionals for ENUMs

Hi 

It's well known that pushing down of WHERE conditions supported for 
built-in data types, operators and functions only. 

So if your main table has columns declared with custom domain (CREATE DOMAIN ID_TYPE AS INT NOT NULL) and you want just to proxy all queries over FDW foreign table needs to be declared as INT. This approach works fine for any domain based on build-in type. But ENUM is a special case. When I declare foreign table with TEXT column it accepts all queries like SELECT * FROM proxy WHERE status = 'active', but 'active' pushed down with explicit type cast 'active'::text and this creates a problem because origin server expects ENUM value ('active' or 'active'::STATUS_TYPE) rather than TEXT. 

CREATE CAST (TEXT as STATUS_TYPE) WITH function to_status_type(text) AS IMPLICIT; 

All attempts to cast text to ENUM type were unsuccessful (probably because PostgreSQL converts ENUM values to INTs on query rewriting stage, but casting works later, when data accessed): 

CREATE CAST (STATUS_TYPE AS TEXT) WITH INOUT AS IMPLICIT; 

Casting in reverse direction works fine, but this dirty trick forces PostgreSQL to convert ENUMs to TEXT (which is less optimal as working internally with INTs) for absolutely all requests, not only forwarded over FDW. 

Questions to developers: 

1. Is there any plans to add "non-strict mode" (configurable via options on server/table/column level) to allow pushing down conditions for all data types? 

2. There is an option that allows to map foreign table column to column with another name. What about adding another option to specify column type to be send to remote server? 

Thanks. 

Tested on 9.3.4 
--
Sergiy Zuban

Gmane