What is random_page_cost and seq_page_cost in your server?
And how many rows does the table have?
On Tue, May 27, 2014 at 2:09 PM, Grzegorz Olszewski <
grzegorz.olszew...@outlook.com> wrote:
> Hi,
>
> I wonder why planner uses Seq Scan instead of Index Scan.
>
> Here is my table (partial):
> con
Hi,
I have a table with zip_code and latitude and longitude.
\d zip_code_based_lng_lat
Table "public.zip_code_based_lng_lat"
Column | Type | Modifiers
++---
zip| character varying(100) |
state | character varying(100) |
city
Why is the following query getting wrong estimation of rows?
I am using Postgresql 9.2.1 with default_statistics_target = 100.
I execute vacuum analyze each night.
explain analyze
SELECT
entity.id AS "Leads_id", entity.type AS "Leads_type" ,
leads.firstname AS "Leads_firstname",
leads.lastname AS
gt;
> dailyest=# show constraint_exclusion;
> constraint_exclusion
> --
> on
> (1 row)
>
> On Fri, Mar 15, 2013 at 11:04 PM, AI Rumman wrote:
>
>> Which version of Postgresql are you using?
>> Have you set constraint_exclusion to parition?
&g
On Fri, Dec 14, 2012 at 3:34 PM, Kevin Grittner wrote:
> Claudio Freire wrote:
>
> > Selectivity is decided based on the number of distinct values on
> > both sides, and the table's name "entity" makes me think it's a
> > table that is reused for several things. That could be a problem,
> > since
4, 2012 at 4:22 PM, Tom Lane wrote:
> > "Kevin Grittner" writes:
> >> AI Rumman wrote:
> >>> Does FK Constraint help to improve performance? Or it is only
> >>> for maintaining data integrity?
> >
> >> I'm not aware of any sit
ot;Kevin Grittner" writes:
> > AI Rumman wrote:
> >> Does FK Constraint help to improve performance? Or it is only
> >> for maintaining data integrity?
>
> > I'm not aware of any situation where adding a foreign key
> > constraint would improve performa
Does FK Constraint help to improve performance? Or it is only
for maintaining data integrity?
On Thu, Dec 13, 2012 at 7:38 PM, Claudio Freire wrote:
> On Thu, Dec 13, 2012 at 8:50 PM, Evgeny Shishkin
> wrote:
> >>> OP joins 8 tables, and i suppose join collapse limit is set to default
> 8. I tho
Why does the number of rows are different in actual and estimated?
The default_statistics_target is set to 100.
explain analyze
select *
FROM (
SELECT
entity.id AS "con_s_id", entity.setype AS "con_s_setype" ,
con_details.salutation AS "con_s_salutationtype", con_details.firstname AS
"con_s_first
, hubert depesz lubaczewski <
dep...@depesz.com> wrote:
> On Tue, Sep 11, 2012 at 07:55:24PM +0600, AI Rumman wrote:
> > I added the excel file for locks data.
>
> well, it worked, but why didn't you just make it text file, in notepad or
> something like this?
>
> > I
at 07:20:28PM +0600, AI Rumman wrote:
> > I have a table as follows:
> > I execued the query:
> > ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
> >
> > The db is stuck. The enity table has 2064740 records;
>
> such alter ta
I have a table as follows:
\d entity
Table "public.entity"
Column|Type | Modifiers
--+-+
crmid| integer | not null
smcreatorid | integer
Thanks. I missed to add the trigger.
Now I added it, but still without partition taking less time compared to
with partition query.
*With partition :- *
explain analyze
> select *
> from table1 as c
> inner join table2 as a on c.crmid = a.activityid and deleted = 0
> where module ='Leads'
> ;
>
leted = 0) AND ((module)::text =
> 'Leads'::text))
>-> Index Scan using crmentity_leads_deleted_idx on
> table1_leads c (cost=0.00..8.27 rows=1 width=280) (actual
> time=0.011..0.011 rows=0 loops=1)
> Index Cond: (deleted = 0)
>
I partitioned a table, but didn't find any improvement in query timing.
The basic table was like as follows :-
\d table1
>Table "public.table1_old"
> Column|Type | Modifiers
> --+-+
>
I can write a query to solve my requirement in any of the followings :-
1.
select *
from a
where NOT EXISTS
(
select 1
from b
where a.id = b.id)
union all
select *
from b
2.
select
(
case when b.id is not null then
b.id
else
a.id
) as id
from a
left join b
on a.id = b.id
Any one plea
Yes I ANALYZE the table, but no change for wastedispace.
On Wed, Sep 21, 2011 at 1:06 PM, Guillaume Lelarge
wrote:
> On Wed, 2011-09-21 at 13:01 +0600, AI Rumman wrote:
> > I am using Postgresql 9.0.1.
> >
> > Using the query http://wiki.postgresql.org/wiki/Show_database_b
I am using Postgresql 9.0.1.
Using the query http://wiki.postgresql.org/wiki/Show_database_bloat, I got
the following result for a table:
-[ RECORD 1 ]+---
current_database | crm
schemaname | public
tablename| _attachments
tbloat
I am using Postgresql 8.2.13 and I found that most of the commits and insert
or update statements are taking more than 4s in the db and the app
performance is slow for that.
My db settings are as follows;
bgwriter_all_maxpages | 300 |
bgwriter_all_percent | 15 |
bgwriter_delay
I have a table in Postgresql 9.0.1 as folllows:
Table "public.crmentity"
Column|Type | Modifiers
--+-+
crmid| integer | not null
smcreatorid | integer
;) means ?
> I see it produces something wrong for to_tsquery:
>
> test=# select replace(' Dhaka University of Bangladesh:*', ' ',':* & ');
>
> replace
> -------
> :* &
explain
SELECT crmentity.crmid, crmentity.setype, crmentity.modifiedtime,
activity.subject,case when ( users.user_name not like '') then
users.user_name else groups.groupname end as user_name, activity.date_start
FROM crmentity INNER JOIN activity ON crmentity.crmid = activity.activityid
and crment
But I am using Postgresql 8.1 and it is not possible to write query as your
one here.
On Thu, Oct 28, 2010 at 11:05 PM, Robert Haas wrote:
> On Tue, Oct 19, 2010 at 7:56 PM, Merlin Moncure
> wrote:
> > On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman wrote:
> >> At presen
Which one is faster?
select count(*) from talble
or
select count(id) from table
where id is the primary key.
I don't know why seq scan is running on the following query where the same
query is giving index scan on other servers:
explain analyze
select *
from act
where act.acttype in ( 'Meeting','Call','Task');
QUERY PLAN
---
Not actualy. I used pagination with limit clause in details query and I need
the total number of records in the detail query.
On Mon, Oct 18, 2010 at 9:52 PM, Josh Kupershmidt wrote:
> On Mon, Oct 18, 2010 at 1:16 AM, AI Rumman wrote:
> > At present for reporting I use following types
At present for reporting I use following types of query:
select crm.*, crm_cnt.cnt
from crm,
(select count(*) as cnt from crm) crm_cnt;
Here count query is used to find the total number of records.
Same FROM clause is copied in both the part of the query.
Is there any other good alternative way to
I need to join two tales say TAB_A and TAB_B, where TAB_A is greater than
TAB_B in size and records.
Which Table should I put first in join order?
Any idea please.
I already sent the mail earlier. but added wrong explain. So I mail it
again.
I have more than 130 records in crm table and I partioned the table with
deleted = 0 key.
It is working fine except that after partioion query is taking more time
than the previous one.
I already set constraint_exclu
I have more than 130 records in crm table and I partioned the table with
deleted = 0 key.
It is working fine except that after partioion query is taking more time
than the previous one.
I already set constraint_exclusion = on; My DB version is Postgresql 8.1
I added the explain anayze for both
I have a table.
\d email_track
Table "public.email_track"
Column | Type | Modifiers
+-+
crmid | integer | not null default 0
mailid | integer | not null default 0
count | integer |
Indexes:
"email_track_pkey" PRIMARY KEY, btree (crmid, mailid) C
Which one is good - join between table or using exists in where condition?
Query 1;
Select a.*
from a
where exists
(
select 1 from b inner join c on b.id1 = c.id where a.id = b.id)
Query 2:
select a.*
from a
inner join
(select b.id from b inner join c on b.id1 = c.id) as q
on a.id = q.id
Any su
Can any one please help me in tuning the query?
explain
select *
from (select * from crmentity where deleted = 0 and createdtime between
(now() - interval '6 month') and now() ) as crmentity
inner join (select * from activity where activitytype = 'Emails' and
date_start between (now() - interval
Whenever I run this query, I get out of memory error:
explain analyze
*select *
email_track.count AS "Emails_Access_Count",
activity.subject AS "Emails_Subject",
crmentity.crmid AS EntityId_crmentitycrmid
*from *
(select * from crmentity where deleted = 0 and createdtime between (now() -
interval
Any more idea, please.
Is table partition a good solution for query optimization?
On Fri, Jun 11, 2010 at 11:09 AM, Amit Khandekar <
amit.khande...@enterprisedb.com> wrote:
>
>
> On 10 June 2010 18:47, AI Rumman wrote:
>
>> I am using Postgresql 8.1 and did not find F
I am using Postgresql 8.1 and did not find FETCH_COUNT
On Thu, Jun 10, 2010 at 6:55 PM, Amit Khandekar <
amit.khande...@enterprisedb.com> wrote:
>
>
> On 10 June 2010 18:05, AI Rumman wrote:
>
>> Could you please give me the link for cursor- How to use it?
>>
>
Could you please give me the link for cursor- How to use it?
On Thu, Jun 10, 2010 at 6:28 PM, Kevin Grittner wrote:
> AI Rumman wrote:
>
> >> Merge Left Join (cost=9500.30..101672.51 rows=2629549 width=506)
>
> > And the query does not return data though I have been
I found only AccessShareLock in pg_locks during the query.
And the query does not return data though I have been waiting for 10 mins.
Do you have any idea ?
On Thu, Jun 10, 2010 at 5:26 PM, Szymon Guz wrote:
>
>
> 2010/6/10 AI Rumman
>
> Can anyone please tell me why the follow
Can anyone please tell me why the following query hangs?
This is a part of a large query.
explain
select *
from vtiger_emaildetails
inner join vtiger_vantage_email_track on vtiger_emaildetails.emailid =
vtiger_vantage_email_track.mailid
left join vtiger_seactivityrel on vtiger_seactivityrel.activi
"Not like" operation does not use index.
select * from vtiger_contactscf where lower(cf_1253) not like
lower('Former%')
I created index on lower(cf_1253).
How can I ensure index usage in not like operation?
Anyone please help.
t;vtiger_account_pkey" PRIMARY KEY, btree (accountid)
"account_account_type_idx" btree (account_type)
"vtiger_account_parentid_idx" btree (parentid)
On Tue, Feb 16, 2010 at 5:43 PM, AI Rumman wrote:
> I am getting seq_scan on vtiger_account. Index is not usi
I am getting seq_scan on vtiger_account. Index is not using.
Could anyone please tell me what the reason is?
explain analyze
select *
from vtiger_account
LEFT JOIN vtiger_account vtiger_account2
ON vtiger_account.parentid = vtiger_account2.accountid
Please have a look at the following explain plan:
explain analyze
select *
from vtiger_crmentity
inner JOIN vtiger_users
ON vtiger_users.id = vtiger_crmentity.smownerid
where vtiger_crmentity.deleted = 0 ;
QUERY
PLAN
I have created a index
create index leadaddress_phone_idx on
leadaddress(regexp_replace((phone)::text, '[^0-9]*'::text, ''::text,
'g'::text));
But the index is not using.
explain select * from leadaddress where
regexp_replace(phone,'[^0-9]*','','g') like '%2159438606';
44 matches
Mail list logo
