Home >


  • Is it possible to set order in which inherited tables a scanned in postgresql?January 28

    I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125. When I run explain select * from measurement where city_id = 125 limi

  • What does "Heap Block" in "Bitmap Heap Scan" mean?January 20

    I have this query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test WHERE timestamp_range @> '2015-01-22 23:00:00'::timestamp AND data_int_array @> '{49, 61}'::integer[]; Which outputs: Bitmap Heap Scan on test (cost=16.74..20.75 rows=1 width=113) (act

  • understanding MYSQL EXPLAIN for query optimizationJanuary 18

    I have a mysql table which contain several million of records. My table have 3 columns indexed named : - uid - application - token I want to select a record with application and uid clause OR application and token clause. Currently, i don't understan

  • Combining Explain and Union

    Combining Explain and UnionDecember 15

    Morning all, I'm trying to quickly compare multiple queries that do similar tasks and I'm wondering if there's a quicker way of lining up the EXPLAIN outputs. To me, this example query should work: EXPLAIN SELECT count(*) AS thetotal, date(datetimefi

  • Why does PSQL not allow hash indexes with Bitmapscan?December 1

    For doing a simple equality query such as EXPLAIN SELECT * FROM table where x = 1; I see that a Bitmap Index scan is done using the BTree indexes. Apparently, Bitmap Index scans disable Hash indexes but I'm curious as to why? Wouldn't the query be fa

  • MySQL optimizer won't use my indexNovember 17

    With the following query: SELECT * FROM geoname LEFT JOIN alternatename ON geoname.geonameid = alternatename.geonameid AND alternatename.isPreferredName = 1 AND alternatename.isoLanguage = 'es' WHERE geoname.fcode = 'ADM2' AND geoname.country = 'ES'

  • MySQL stuck in EXPLAIN statementAugust 7

    I am trying to debug the following query. # Projects with at least 50 members with at least 240 yearly commits each select project_id, count(*) as active_members from ( select project_members.repo_id as project_id, project_members.user_id, count(*) f

  • What do the shared buffers metrics for various types of index scans represent?August 4

    TL;DR: When an "EXPLAIN (BUFFERS)" shows an "Index Scan", it does not show the number of pages needed to be read from the index. Is it just omitting that number, or does it actually not read pages (and I misunderstand how the index wor

  • Should I stop an awfully inefficient MySQL DELETE statement?July 8

    I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode: DELETE table FROM ( SELECT MAX(id) id, field FROM table GROUP BY field ) temp_table INNER JOIN table ON table.field = temp_table.field WHERE table.id != t

  • Mysql Index for concat selectApril 30

    For Below Query SELECT MAX(CONCAT(date, ' ', last_entry)) AS LAST_LOG FROM entry_log WHERE TRIM(LEADING 0 FROM card_no)='2948' I've index on date card_no date,last_entry date,last_entry,card_no My Explain Shows id select_type table type possible_keys

  • Why is this MySQL query "using where" instead of "using index"?

    Why is this MySQL query "using where" instead of "using index"?April 23

    Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join op_adv_effects._additional as a1 on c1.patid = a1.patid where c1.adid

  • Amazon Redshift: "*SELECT* 1" vs. volt_dt_0 vs. volt_tt_51343b6aa3bd4April 11

    I've noticed subqueries in Amazon Redshift can be represented in the explain plan in 3 separate ways: -> XN Subquery Scan "*SELECT* 1" -> XN Subquery Scan volt_dt_0 -> XN Seq Scan on volt_tt_51343b6aa3bd4 "SELECT 1" and volt_d

  • MySQL - How can I improve this query? EXPLAIN attached

    MySQL - How can I improve this query? EXPLAIN attachedFebruary 12

    I have a query that I feel should go much faster then it does. Right now it'll execute around 1,5xx secs each time. I don't have any advanced knowledge of looking at the EXPLAIN and draw conclusions. Maybe someone can help? The query has all its ID-f

  • How to optimize search restricted by boolean column?January 29

    I want to optimize a query1 of the form SELECT yid, xid FROM x WHERE is_principal; Here, is_principal is a non-null boolean column of x. If I run EXPLAIN QUERY PLAN on this query, the output I get is 0|0|0|SCAN TABLE x I'd like to turn this SCAN into

  • Is there a parser for Postgres' auto_explain to make it easier to read and understand?December 5

    We have a number of Postgres Functions that each call a number of other Postgres Functions. If you simply EXPLAIN ANALYSE a call like SELECT parent_function(), the output is minimal, since it doesn't dive into the calls the parent_function makes. Tha

  • Statements in State "Copying to tmp table" disrupt performance November 17

    On a MySQL database (version 5.1.73) a Magento-Shop executes a statement (A) that uses distinct, see http://pastebin.com/A2sy45nF and therefore is "Using temporary" according to "explain". Under certain load conditions (when an involve

  • Why does a SELECT statement dirty cache buffers in Postgres?October 27

    I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output. From the documentation, "dirtied" indicates: The number of blocks dirtied indicates

  • Optimizing query - understanding explainOctober 21

    I've got a query (partly generated by django orm, but can be modified). For some reason, It's very slow (between 1.5 -3 seconds, without having lots of data on the system) . I've got an "explain" from my newrelic trace. But I don't really know h

  • Almost identical queries but large time difference in executionSeptember 3

    Exhibit A SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at >= 1408060800 && created_at <= CURRENT_TIMEST

  • Disc IO for query in MongoDBAugust 11

    is there a way to tell how much data MongoDB is reading from disc and how much from cache for a query ? I looked at the explain()-Function but did not find this kind of informations. Thanks, Andre --------------Solutions------------- It is not possib

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.765 s.